Most Recent Date

[juliaisabel]

It seems that this is really similar to several other questions posed, but
I'm going to pose it anyway because I don't see an answer that fits my
question just right.

I have 2 tables. 1 table is Master Company List - where each company is
defined by a broker id. This table is linked by the broker id to the master
property listing table which contains a field for the date the property was
added. In the Master Company list there are about 75 records, in the Master
Property Listing there are about 3000 records, each Company has several
hundred properties associated.

What I am trying to do is to display the company name along with the most
recent date a property was added. So far this is what my query looks like,
it is (clearly) missing the piece to filter for only most recent date.

SELECT [Master Company List].Name, [MASTER LISTINGS WITH BROKER
ID].property_date_added
FROM [Master Company List] INNER JOIN [MASTER LISTINGS WITH BROKER ID] ON
[Master Company List].[Broker ID] = [MASTER LISTINGS WITH BROKER ID].[Broker
Number];

 

[Gary]

It seems that this is really similar to several other questions posed, but
I'm going to pose it anyway because I don't see an answer that fits my
question just right.

I have 2 tables. 1 table is Master Company List - where each company is
defined by a broker id. This table is linked by the broker id to the master
property listing table which contains a field for the date the property was
added. In the Master Company list there are about 75 records, in the Master
Property Listing there are about 3000 records, each Company has several
hundred properties associated.

What I am trying to do is to display the company name along with the most
recent date a property was added. So far this is what my query looks like,
it is (clearly) missing the piece to filter for only most recent date.

SELECT [Master Company List].Name, [MASTER LISTINGS WITH BROKER
ID].property_date_added
FROM [Master Company List] INNER JOIN [MASTER LISTINGS WITH BROKER ID] ON
[Master Company List].[Broker ID] = [MASTER LISTINGS WITH BROKER ID].[Broker
Number];

Hello,

Add a WHERE statement at the end, such as
<snip> [Broker Number] WHERE (property_date_added>#1/1/2005#)

or whatever date you want to sort by. The WHERE clause would go at the end.

HTH,
Gary

 

[juliaisabel]

Thank you Gary, however, I would need it to be strictly the most recent for
each company. The "where" statement could be after or before any date I
might use as a control.

This query is (supposed to be) designed to instantly show WHEN the newest
property was added for each company.

For sake of expediency, let's say that company A has 10 properties, the most
recent of which was added 07/26/2005. Company B, has 100 properties, the
most recent of which was added 07/15/2005. The results I am looking for
would show

Company A 07/26/2005
Company B 07/15/2005

etc.

All companies are listed in Master Company List Table.
All properties are listed in Master Property Table.
The broker id is the identifier.
This is a one to many relationship.

What I do not know how to do is how to choose the greatest number/date out
of all the property records, for each company in the property added date
field.
(I've searched largest, most recent, most current number/date in all online
knowledge and haven't found what I need so far)

Can anyone figure this one out?

It seems that this is really similar to several other questions posed, but
I'm going to pose it anyway because I don't see an answer that fits my
question just right.

I have 2 tables. 1 table is Master Company List - where each company is
defined by a broker id. This table is linked by the broker id to the master
property listing table which contains a field for the date the property was
added. In the Master Company list there are about 75 records, in the Master
Property Listing there are about 3000 records, each Company has several
hundred properties associated.

What I am trying to do is to display the company name along with the most
recent date a property was added. So far this is what my query looks like,
it is (clearly) missing the piece to filter for only most recent date.

SELECT [Master Company List].Name, [MASTER LISTINGS WITH BROKER
ID].property_date_added
FROM [Master Company List] INNER JOIN [MASTER LISTINGS WITH BROKER ID] ON
[Master Company List].[Broker ID] = [MASTER LISTINGS WITH BROKER ID].[Broker
Number];

Hello,

Add a WHERE statement at the end, such as
<snip> [Broker Number] WHERE (property_date_added>#1/1/2005#)

or whatever date you want to sort by. The WHERE clause would go at the end.

HTH,
Gary

 

[Dirk Goldgar]

It seems that this is really similar to several other questions
posed, but I'm going to pose it anyway because I don't see an answer
that fits my question just right.

I have 2 tables. 1 table is Master Company List - where each company
is defined by a broker id. This table is linked by the broker id to
the master property listing table which contains a field for the date
the property was added. In the Master Company list there are about
75 records, in the Master Property Listing there are about 3000
records, each Company has several hundred properties associated.

What I am trying to do is to display the company name along with the
most recent date a property was added. So far this is what my query
looks like, it is (clearly) missing the piece to filter for only most
recent date.

SELECT [Master Company List].Name, [MASTER LISTINGS WITH BROKER
ID].property_date_added
FROM [Master Company List] INNER JOIN [MASTER LISTINGS WITH BROKER
ID] ON [Master Company List].[Broker ID] = [MASTER LISTINGS WITH
BROKER ID].[Broker Number];

I think you're looking for something like this ("air SQL"):

SELECT
C.Name As CompanyName,
Max(L.property_date_added) As LastAddDate
FROM
[Master Company List] As C
INNER JOIN
[MASTER LISTINGS WITH BROKER ID] As L
ON C.[Broker ID] = L.[Broker Number]
GROUP BY C.Name;

Don't let the C and L confuse you -- I just picked them as aliases for
the tables so that I wouldn't have to type in those long names more than
once.

Later work in Access will be easier, by the way, if you avoid using
spaces in the names of tables, fields, and other objects. Then you
don't need those annoying brackets.

 

[juliaisabel]

It worked, of course! Thanks so much... I don't know why I didn't think of
min and max.... Sorry for such a basic question, sometimes I guess you can't
see the forest for the trees.

Thank you! (PS you will notice the table and field names... thanks again)

SELECT MasterCompanyList.CoName, MasterCompanyList.ScrapingSchedule,
MasterCompanyList.Scraper, Max(MasterListingsWithBrokerID.propertydate_added)
AS LastAddDate, [LastAddDate]+[DaystoScrape] AS [Overdue After]
FROM MasterCompanyList INNER JOIN MasterListingsWithBrokerID ON
MasterCompanyList.BrokerID = MasterListingsWithBrokerID.BrokerNumber
GROUP BY MasterCompanyList.CoName, MasterCompanyList.ScrapingSchedule,
MasterCompanyList.Scraper, [LastAddDate]+[DaystoScrape],
MasterCompanyList.DaystoScrape
HAVING (((MasterCompanyList.ScrapingSchedule)<>"Never"));