Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Nottingham, Nottinghamshire, United Kingdom
    Thanked 0 Times in 0 Posts

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

    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]

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts