Returning only min and max dates in Access query

[brewcrew77]

Here's a sample recordset in the table I'm looking at:

Code Subcode Date
10051 26 11/1/2006
10051 26 11/25/2006
10051 26 12/2/2006
10052 10/5/2006
10052 11/7/2006
10052 12/1/2006

I want to write a query that will only return rows with the minimum and maximum date for each code/subcode combination. Using the recordset above, the results of this query would look like this:

Code Subcode Date
10051 26 11/1/2006
10051 26 12/2/2006
10052 10/5/2006
10052 12/1/2006

Any advice would be much appreciated. Thanks!

[MMcCarthy]

Using a union query would return the format you've suggested.

SELECT Code, Subcode, Max([Date]) As [Date]
FROM TableName
UNION
SELECT Code, Subcode, Min([Date]) As [Date]
FROM TableName
GROUP BY Code, Subcode;
 

Alternatively, you could return two separate date fields as follows:

SELECT Code, Subcode, Min([Date]) As MinDate, Max([Date]) As MaxDate
FROM TableName
GROUP BY Code, Subcode;
 

Mary

[NeoPa]

You may want to add :

ORDER BY Code, Subcode, [Date]

to first version or :

ORDER BY Code, Subcode

to the second if the order is important. Must be before the ';' though.