How do I return only the record with the most recent date?

[Nelson]

I have a query that asks the user for a date range and returns all the
records that have a date that falls within that date range. I only want to
return the most recent date though. See the practical example below:

Data:
Company Date Quote
Cogswell Cogs 1/10/04 1
Cogswell Cogs 2/12/04 2
Cogswell Cogs 4/12/04 3
Cogswell Cogs 5/11/04 4
Cogswell Cogs 5/12/04 5
Able MFG 1/8/04 6
Able MFG 3/17/04 7
Able MFG 4/7/04 8
Able MFG 5/12/04 9

In the query the user enters beginning date of 3/1/04 and ending date of
5/11/04.
Right now the query returns the following:
Company Date Quote
Cogswell Cogs 4/12/04 3
Cogswell Cogs 5/11/04 4
Able MFG 3/17/04 7
Able MFG 4/7/04 8

But I want it to return only this:
Cogswell Cogs 5/11/04 4
Able MFG 4/7/04 8

How do I do this?

Thanks,
Chris

 

[ChrisJ]

Try...

Select Company, max(date) from yourtable
Group by Company
where date between #3/1/04# and #5/11/04#

 

[John Spencer (MVP)]

SELECT Company, Max([Date]) as LastDate
FROM YourTable
WHERE [Date] Between #3/1/04# AND #5/11/04#
GROUP BY Company

If you need other columns of information from the query, you might try a two
query approach.

Save the Above query and then open another query on the original table and join
it to the results of this query using the company fields and the date fields.

SELECT Company, [Date], Quote
FROM YourTable INNER JOIN SavedQuery