Does anyone have a clue?

[sf129]

These are 6 different rows on a table. I need to combine dates that are consecutive.
start end
06/02/1999 06/03/1999
06/09/1999 06/29/1999
06/09/2000 06/30/2000
07/01/2000 07/06/2000
08/21/2000 08/31/2000
09/01/2000 09/12/2000

I would like to 4 rows instead of 6 reading like this:
start end
06/02/1999 06/03/1999
06/09/1999 06/29/1999
06/09/2000 07/06/2000
08/21/2000 09/12/2000

Can anyone tell me how to do this?

 

[llkhoutx]

I think that you're going to have to do this manually with DAO or ADO code, that is, go through the mechanics of how you'd test manually, then code it. It not trivial.

Take the initial record and save the interval times (hereinafter referred to as "the Times") and compare them against each record to see if contiguous or overlapping, if contiguous, adjust the Times accordingly, if overlapping also adjust the Times accordingly. Mrked each adjacent or overlapping record as it is included in Times. When you have compared the Times against each record and adjsted accordingly, save the Times in a temporary table.

Repeat with a new unmarked interval until all records are marked and appropriate intervals are saved in the temporary table. The temporay table has your complete list of discrete intervals.

This is a brute force thing and you need not worry about the time it takes unless you have hundreds of records, in which event you'll have to something more elegant like building a decision tree comparing adjacent records.

 

[sf129]

I do have hundreds of records. That is just a sampling from a large table.

 

[llkhoutx]

Then sort your records chronologically and check adjacent records.