Select record with most current date

[Guest]

I have a table with fields as follows:

CommentsID
ClaimID
Latest Report (inserts date a new comment is entered)
Comments (memo field)

I would like to be able to view the most current comment for each ClaimID
record.

I have the following select query, however, it just shows me the one record
which is the most current comment not for each ClaimID.

SELECT [tbl_RM Comments].ClaimID, [tbl_RM Comments].LatestReport, [tbl_RM
Comments].[PME Comments]
FROM [tbl_RM Comments]
WHERE ((([tbl_RM Comments].LatestReport)=(SELECT Max(LatestReport) FROM
[tbl_RM Comments])));

Please help.

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

 

[Guest]

That was awesome! The fix worked and I'm well on my way to a great report.

I will also be looking more closely at the site you quoted below.

By the way, I used the Sub-select in the FROM clause (Item 4) solution.

Thank you.

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table with fields as follows:

CommentsID
ClaimID
Latest Report (inserts date a new comment is entered)
Comments (memo field)

I would like to be able to view the most current comment for each ClaimID
record.

I have the following select query, however, it just shows me the one
record
which is the most current comment not for each ClaimID.

SELECT [tbl_RM Comments].ClaimID, [tbl_RM Comments].LatestReport, [tbl_RM
Comments].[PME Comments]
FROM [tbl_RM Comments]
WHERE ((([tbl_RM Comments].LatestReport)=(SELECT Max(LatestReport) FROM
[tbl_RM Comments])));

Please help.