First Person by date

[agibsonsw]

Hello (Access 2003)
I have a table with Dept, FirstName, Surname, StartDate.
I'm trying to build a Totals Query in Design View to show the name of the first person to join each department, but am struggling to get it to work.
I've Grouped By Dept, added StartDate which is sorted in ascending order and chosen Min as the function. But when I add and group by the Surname&Firstname
it doesn't give me the correct person? Thanks for any advice, Andy.

[HansV]

You can do this in two steps:

1. Create a totals query based on the table.
Add only the department and the start date to the query grid.
Change the total option for the start date to Min.
Save as (for example) qryFirstDate.
This query will return the earliest start date for each department.

2. Create a new query based on the table and qryFirstDate.
Join the table to the query on Dept vs Dept, and also on StartDate vs MinOfStartDate.
Add fields from the table to the query grid.
This query will return the data you want.

Note: if there are two persons with the earliest start date for a department, both will be returned.

[agibsonsw]

Thank you.
Could you suggest why my first attempt wouldn't work? It gave me the dept, the date but then couldn't pick up the name of the first person? Thanks, Andy.

[HansV]

If you group by Dept and by Surname&FirstName, the query will return a record for each employee, which is not what you want.
The reason for doing it in two steps is because the problem dicates it:
1. Find the earliest date for each department.
2. For each department, find the person whose start date is the one determined in step 1.

[agibsonsw]

Thanks again.