Query to find latest entries in a table (Access 2000)

Topic

New Reply

I have a table ResHistory in which I log dated events. Fields are [ResHistoryID] which is autonumber, [ResID] which links to the resident database, [ResHistoryDate] which is the event date, plus various other fields that record information about the event.

I need to produce a query that will list the last entry by [ResHistoryDate] in the history log for EVERY [ResID] but every attempt I have made has resulted in multiple records for each [ResID]. I will be using this query as a datasource for another query which will let me create a report with details from the Resident table and from the last entry for that resident in the ResHistory table.

How do I exclude the multiple occurrences of [ResID]

Reply | Quote

Replies

I would do it in two steps (there probably is a sneaky way to do it in one step)

1. Create a Totals query based on ResHistory. Add ResID (Group By) and ResHistoryDate (Max). Save this query.

2,. Create a new query. Add ResHistory and the query from step 1. Join them on ResID and on the ResHistoryDate field (in the query, it will be named MaxOfResHistoryDate). Add the fields you want to see to the query grid.

This query won’t be updateable, but that isn’t necessary for a report.

Reply | Quote

I would do it in two steps (there probably is a sneaky way to do it in one step)

1. Create a Totals query based on ResHistory. Add ResID (Group By) and ResHistoryDate (Max). Save this query.

2,. Create a new query. Add ResHistory and the query from step 1. Join them on ResID and on the ResHistoryDate field (in the query, it will be named MaxOfResHistoryDate). Add the fields you want to see to the query grid.

This query won’t be updateable, but that isn’t necessary for a report.

Reply | Quote