Searching For Last Item

[andresdm]

Hello,

I have a table where I am tracking comments for a record in another table. The comments table has it's key, a claim ID, a comment date, a follow up person, and the actual comment.

When using the query tool, how do i query to show the last comment and last follow up person according to the last date a comment was entered?

If I use the group by First or Last on Follow up and Comment, I keep getting the first or last by alphabetical order...

What i want to see is when i run a query where item 37 has four records in the comment table, display 1 record with "Allison" as follow up person and "GEO..." as comment.

[June7]

Be careful with Last and First, Max and Min are usually safer.

If you want to return the entire record this requires either DMax() or intermediate query.

SELECT * FROM tablename WHERE CM_Last_Update_Date = DMax("CM_Last_Update_Date", "tablename", "CM_Claims_ID=" & [CM_Claims_ID]);

or

A simple aggregate query will return the latest date for each ID.
SELECT CM_Claims_ID, Max(CM_Last_Update_Date) AS LatestDate FROM tablename GROUP BY CM_CLAIMS_ID;

Now join that query to the table and apply criteria to the CM_Last_Update_Date field:
= [LatestDate]

or

review http://allenbrowne.com/subquery-01.html especially the example for Top N Records Per Group

[andresdm]

I think it worked!! I'll keep testing but it's looking good! Thank you.