Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    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
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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
  •