By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Returning only min and max dates in Access query

P: 1
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!
Dec 11 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Using a union query would return the format you've suggested.

Expand|Select|Wrap|Line Numbers
  1. SELECT Code, Subcode, Max([Date]) As [Date]
  2. FROM TableName
  3. UNION
  4. SELECT Code, Subcode, Min([Date]) As [Date]
  5. FROM TableName
  6. GROUP BY Code, Subcode;
  7.  
Alternatively, you could return two separate date fields as follows:

Expand|Select|Wrap|Line Numbers
  1. SELECT Code, Subcode, Min([Date]) As MinDate, Max([Date]) As MaxDate
  2. FROM TableName
  3. GROUP BY Code, Subcode;
  4.  

Mary
Dec 11 '06 #2

NeoPa
Expert Mod 15k+
P: 31,494
You may want to add :
Expand|Select|Wrap|Line Numbers
  1. ORDER BY Code, Subcode, [Date]
to first version or :
Expand|Select|Wrap|Line Numbers
  1. ORDER BY Code, Subcode
to the second if the order is important. Must be before the ';' though.
Dec 12 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.