Grouping Question

[darude]

Hello,
Below are the records I want to group in a query.

StudentID EmployerID StartDate EndDate ReasonLeft
211061 82 9/1/2006 1/31/2007 Rollover
211061 82 2/1/2007 6/30/2007

This is the result of my group by query maxing the ResonLeft field. Ultimately, I want to group the records so that the ReasonLeft field matches what corresponds to the EndDate field. Can someone help provided this makes sense. Thank you in advance, also below is the SQL Statement.

StudentID EmployerID StartDate EndDate ReasonLeft
211061 82 9/1/2006 6/30/2007 Rollover


SELECT Jobs.StudentID, Jobs.EmployerID_fkey, Min(Jobs.StartDte) AS MinOfStartDte, Max(Jobs.EndDate) AS MaxOfEndDate, Last(Jobs.ReasonLeft) AS LastOfReasonLeft
FROM Jobs
WHERE (((Jobs.StartDte) Between #9/1/2006# And #6/30/2007#))
GROUP BY Jobs.StudentID, Jobs.EmployerID_fkey
HAVING (((Jobs.StudentID)="211061"));

 

[dhookom]

The simple answer is to create a group by query like:

SELECT StudentID, EmployerID_fkey, Max(EndDate) AS MaxOfEndDate
FROM Jobs
GROUP BY StudentID, EmployerID_fkey;

Then create another query with the Jobs table and the group by query above joiningn the StudentID, EMployerID_fkey, and MaxOfEndDate/EndDate fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]