next date??

[joemeshuggah]

Is there a way to align dates using SQL in Access? My problem is as follows:

I have a table that only shows the start date of a particular contract for
each record(no column for end date). I want to query the table so that if a
record has a second (third, fourth, etc) start date, the next start date
minus one day is reflected as the end date of the contract being queried.
For example:

Customer A has contract effective dates of 01/01/06, 02/02/07, and 03/03/08.
Customer B has a contract effective date of 04/04/05

Is there a way to query using SQL such that the following is the result set:

Customer Start Date End Date
A 01/01/06 02/01/07
A 02/02/07 03/02/08
A 03/03/08 NULL
B 04/04/05 NULL

 

[John Spencer]

A query that looks like the following should work

SELECT A.Customer, A.StartDate
, (SELECT DateAdd("d',-1,Min(B.StartDate))
FROM YOUR Table as B
WHERE B.Customer = A.Customer
and B.StartDate > A.StartDate) as EndDate
FROM YourTable as A

An altenative that should also work

SELECT A.Customer
, A.StartDate
, DateAdd(Min("d",-1,B.StartDate)) as EndDate
FROM YourTable as A LEFT JOIN YourTable as B
ON A.Customer = B.Customer
AND A.StartDate < B.StartDate
GROUP BY A.Customer, A.StartDate
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..