query to select latest records from a table. 1

[Juddy58]

Hi, ive been stuck on this query and cant seem to get it working, i have done several searches and made a bit of progress.

I have one table tblAssetReview
fldAssetReviewID fldAssetID fldReviewDate
1 300 1/1/04
2 200 1/2/04
3 300 4/4/04
4 400 1/1/04
5 200 1/1/05

What i need to do is return fldAssetReviewID for each asset for the latest date so i would end up with
fldAssetReviewID fldAssetID fldReviewDate
3 300 4/4/04
4 400 1/1/04
5 200 1/1/05
two records were removed because there are records with a later review date.

I sort of know how to go about this by using a nested select statement but can't seem to get
the correct results, i either get a syntax error or it will only return one record.

I have tried:
SELECT A.fldAssetReviewID, A.fldReviewDate, *
FROM tblassetreview AS A
WHERE (((A.fldReviewDate)=(Select Max(B.fldReviewDate) From tblAssetReview as B)));

Doing it this way i only get one record, if i try to set up a join between A and B i get an error
and the query doesn't work.I originally thought about using grouping to get latest date but that
would result in me getting incorrect values in fldAssetReviewID.
Any advice would be greatly appreciated.
Thanks
Justin

 

[dhookom]

SELECT A.fldAssetReviewID, A.fldReviewDate, *
FROM tblassetreview AS A
WHERE A.fldReviewDate=(Select Max(B.fldReviewDate)  From tblAssetReview as B WHERE A.fldAssetID = B.fldAssetID);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Juddy58]

Thanks DHookom works perfect
A star for you!

 

[PHV]

Another way:
SELECT A.fldAssetReviewID, A.fldAssetID, A.fldReviewDate
FROM tblAssetReview AS A INNER JOIN (
SELECT fldAssetID, Max(fldReviewDate) AS LatestDate
FROM tblAssetReview GROUP BY fldAssetID
) AS B ON A.fldAssetID=B.fldAssetID AND A.fldReviewDate=B.LatestDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886