Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Group - Combine Consecutive Dates into a Date Range

0 views
Skip to first unread message

MSJ

unread,
Jan 6, 2010, 4:45:44 PM1/6/10
to
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?

vanderghast

unread,
Jan 6, 2010, 5:53:14 PM1/6/10
to
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


"MSJ" <mike...@gmail.com> wrote in message
news:a0abe1ed-f5db-43a8...@j14g2000yqm.googlegroups.com...

Message has been deleted

MSJ

unread,
Jan 7, 2010, 9:40:05 AM1/7/10
to
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!

On Jan 7, 9:18 am, MSJ <mikesj...@gmail.com> wrote:
> 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.


>
> On Jan 6, 5:53 pm, "vanderghast" <vanderghast@com> wrote:
>
>
>
> > 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
>

> > "MSJ" <mikesj...@gmail.com> wrote in message


>
> >news:a0abe1ed-f5db-43a8...@j14g2000yqm.googlegroups.com...
>
> > >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?- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

vanderghast

unread,
Jan 7, 2010, 10:00:54 AM1/7/10
to
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

0 new messages