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

Grouping in a Query? Kind of an Odd Sort Problem

P: 27
I have a table with names and a linked table with start and end dates for activities
They want a report (which I am exporting to excel) listed by the earliest end date for an activity. I can create a query that lists names alphabetical and then all of the activities for that person by earliest end date. But they also want the list not in alphabetical order but in order of the earliest end date of all activies for that person.
There are multiple activity records and therefore multiple enddates for each person

Example Anderson End Dates 2/15/10, 3/12/10, 1/14/10
Frank End Dates 2/10/20
Jones End Dates 1/12/10, 4/15/10

Creates List

Jones
1/12/10
4/15/10

Anderson
1/14/10
2/15/10
3/12/10

Frank
2/10/20

I'm not sure how to get the names in order of the earliest start in their list of activities.
Apr 4 '10 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
Not sure I understand you correctly ... but
You could prepend the persons name with the min([End Date])

Expand|Select|Wrap|Line Numbers
  1. select min([End Date]) & Name as Nme,[End Date]
  2. From theTable
  3. Join theOherTable join conditions
  4. group by Name,[End Date]
  5. order by Nme
  6.  
or perhaps more simply
Expand|Select|Wrap|Line Numbers
  1. select min([End Date]) as Dte, Name,[End Date]
  2. From theTable
  3. Join theOherTable join conditions
  4. group by Name,[End Date]
  5. order by Dte,Nme
  6.  
Apr 6 '10 #2

Post your reply

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