Totals query problem

[Dave]

I have a table of schedule data:

ID System1 Milestone 1 Planned Date Actual Date
ID2 System1 Milestone 2 Planned Date Actual Date
ID3 System1 Milestone 3 Planned Date Actual Date
ID4 System2 Milestone 1 Planned Date Actual Date
ID5 System2 Milestone 2 Planned Date Actual Date
ID6 System2 Milestone 3 Planned Date Actual Date

I need to find, for each system, the ID of the earliest and latest
milestone records. I can group by System and find Min(Planned Date or
Actual Date) and Max(Planned Date or Actual Date), but the query can't
include ID because then the query returns all of the records. How do I
devise a query or set of queries that will return the first and last
dates for each system together with the ID of the record they belong
to?

Thanks
Dave

 

[Allen Browne]

See:
Queries: Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

A pair of subqueries might be the simplest approach.

 

[Dave]

Allen -
Thanks - this points me in the right direction.
Dave