Group - Combine Consecutive Dates into a Date Range

  • Thread starter Thread starter MSJ
  • Start date Start date
M

MSJ

I am attempting to combine all records within a table into a date
range.

John Doe | 1/1/2010
John Doe | 1/2/2010
John Doe | 1/3/2010
John Doe | 1/10/2010
John Doe | 1/11/2010
John Doe | 1/12/2010
John Doe | 1/15/2010

I would like to see
Name | Start Date | End Date
John Doe | 1/1/2010 | 1/3/2010
John Doe | 1/10/2010 | 1/12/2010
John Doe | 1/15/2010 | 1/15/2010

Any recommendations?
 
Rank the dates per whoID. Use your preferred algorithm.

Data will be like:

J.D. 1/1/2010 1
J.D. 1/2/2010 2
J.D. 1/3/2010 3
J.D. 1/10/2010 4
J.D. 1/11/2010 5
J.D. 1/15/2010 6




Make a second query:

SELECT whoID, MIN(date), MAX(date)
FROM previousQuery
GROUP BY whoID, date - rank




That's all.



Vanderghast, Access MVP
 
I don't see how this would work. Wouldn't that just give me one
line...
J.D. 1/1/2010 1/15/2010

I want 3 seperate lines as there are 3 different sets of unique
consecutive dates.
 
Nevermind I do understand what you are saying now... That makes
perfect sense and is a fantastic way to approach this problem.

Thank you so much for this solution!
 
There are times where a simple solution seems to be not the good one, just
because it is so simple, but works none the less :-)


Vanderghast, Access MPV
 
Back
Top