Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query to return first record for each Person (XP)

    I am trying to print a list of people who were scanned into a table along with the time they were scanned. The list contains multiple instances of people's ID but we only want the first record for each person. DISTINCT only worrks if I don't output the time, any suggestions as to the best approach?

  2. #2
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to return first record for each Person (XP)

    To pull the most recent record from a similar situation I use a top values query with just 1 return or "top value". sometimes it is a little difficult to manually put a number in the top values drop down. I think I just type in a one and hit enter.

    Carla

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to return first record for each Person (XP)

    You can do this by creating two queries:

    1) Create a query based on your table.
    Add only the Person ID field and thedate/time field to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Leave the Total option for the ID field as it is (Group By), but change the Total option for the date/time field to Min.
    Save this query as (for example) qryFirstRec.

    2) Create a new query based on your table and on the query you just created and saved.
    Join the two on the Person ID field and on the date/time field (in the query, it's called MinOf...)
    Add * from the table to the query grid (or just the fields you need)
    This query will return the first record for each person.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to return first record for each Person (

    Min Value - Beautiful! of course its obvious now but I would have never thought of it.
    Thanks Hans

Posting Permissions

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