Query to return first record for each Person (XP)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote