Group By or MAX Question

  • Thread starter Thread starter Toria
  • Start date Start date
T

Toria

I have a root ID field. I have a close date field. There can be more than one
record with the same root ID. I need to pull only one of them with the latest
close date.

Thank you.
 
Many possible solutions, the easiest one is probably to do it in two
queries:

SELECT id, max(closeDate) AS mdate
FROM tableNameHere
GROUP BY id

saved as q1. Then:

SELECT a.*
FROM tableNameHere AS a INNER JOIN q1
ON a.id=b.id AND a.closeDate = q1.mdate



Other solutions at http://www.mvps.org/access/queries/qry0020.htm



Vanderghast, Access MVP
 
I have a root ID field. I have a close date field. There can be more than one
record with the same root ID. I need to pull only one of them with the latest
close date.

Thank you.
Click to expand...

A Subquery will do this for you; use a criterion on the close date field of

=(SELECT Max(X.[Close date] FROM yourtable AS X WHERE X.[Root ID] =
yourtable.[Root ID])

If there are two records with the same close date you'll get both. Does your
table have a Primary Key?
 
Back
Top