Need Help with Complex Query

[JohnBlaze]

Hi
I have been asked to perform a clean up on our companies contract records, and have not been able to figure out how to do this
Basicaly, a contract can have more than 1 document associated to it, and, each document has an end and start date. When a new version of the document is applied to the contract the dates must not overlap.
There are 4 columns in the system. Contract #, Document #, Start Date, End Date.
As an example, lets say the contract ID is 123, and the document ID is 4.
CONTRACT # DOC # START END
123 4 1/1/1999 12/31/1999
123 4 1/1/2000 12/31/9999
123 4 6/1/2001 10/31/2001
123 4 11/01/2001 12/31/9999
123 4 1/1/2003 12/31/2004

In this example the 2nd and 4th versions should be written out to a table as a problem. The 2nd should have had an end date of 5/31/2001 and the 4th should have had an end date of 12/31/2002

Any thoughts on how to tackle this? Thank you.

 

[Trendsetter]

If you make a query that has criteria in the enddate of:
>#01/01/9998# - that way all of the records that appear as problems will be isolated you could turn this into a mqke table query if you wish.

 

[JohnBlaze]

Trendsetter,
Thank you for your response. However, the dates in question will not always be 12/31/9999. Its just that the next documents new date must be higher than the previous documents low date