Collapsing continuous records into one

[ggrandon]

This seems like it should be doable but I can't figure it out. I want to
reduce multiple records to one where the start date of one record is the day
after the end date of another where EEnum and Bondtype are equal. Thanks in
advance for any help you can provide. Example below.
Current table
EEnum Bondtype start end
11111 01 1/1/2008 6/30/2008
11111 01 7/1/2008 8/5/2008
11111 01 8/6/2008 12/31/9999
22222 01 1/1/2008 6/30/2008
22222 01 7/25/2008 12/31/9999

Desired
11111 01 1/1/2008 12/31/9999
22222 01 1/1/2008 6/30/2008
22222 01 7/25/2008 12/31/9999

 

[Sylvain Lafontaine]

This query will give you the beginning of each sequence:

SELECT *
FROM Table1 AS d1
WHERE Not Exists (
Select * from Table1 d2 where d1.EEnum = d2.EEnum and d1.BondType =
d2.BondType
and d2.enddate = d1.startdate -1
)
WITH OWNERACCESS OPTION;


This one will give you all the endings:

SELECT *
FROM Table1 AS d3
WHERE Not Exists (
Select * from table1 d4 where d3.EEnum = d4.EEnum and d3.BondType =
d4.BondType
and d4.startdate = d3.enddate +1
)
WITH OWNERACCESS OPTION;


Putting these two together, setting the second as a subquery for the first,
you get:

SELECT d1.*, (Select Min (d3.enddate)
from Table1 d3
Where Not Exists (
Select *
from Table1 d4
where d3.EEnum = d4.EEnum and d3.BondType = d4.BondType
and d4.startdate = d3.enddate +1
)

And d3.EEnum = d1.EEnum and d3.BondType = d1.BondType and d3.startdate >=
d1.startdate

) AS enddate2

FROM Table1 AS d1
WHERE (((Exists (Select * from Table1 d2 where d1.EEnum = d2.EEnum and
d1.BondType = d2.BondType
and d2.enddate = d1.startdate -1
))=False))
WITH OWNERACCESS OPTION;


Notice that I'm not sure if things like « d3.enddate +1 » won't give you an
error for dates which are already at the maximum (12-31-9999). I don't have
any problem here but maybe you should replace them with 12-30-9999 or even
to use the Null value instead.

Sorry for the poor formatting but this is Access.