Compare records in same table

[JoeP]

I have a table that has a Start field and an End field both of which are
Date/Time type fields. I want to compare the records in the table for data
entry errors since it is not possible to have two activities occurring at the
same time.
For example if one record has a Start of 1:00 PM on May 24 and and End of
2:00 PM on May 24, I want to test to make sure that no other record in the
same table has a Start or and End in the period of 1:00 PM to 2:00 PM on May
24.
Is this possible, if so I would greatly appreciate any help.
I am using Access 2003.

 

[Douglas J. Steele]

Allen Browne has an introduction to the topic at
http://www.allenbrowne.com/appevent.html

 

[John Spencer]

If you are trying to do this after the table is completely populated
then I think you might try something like the following.

SELECT A.*
FROM YourTable As A INNER JOIN YourTable As B
ON A.StartDateTime <= B.EndDateTime
AND A.EndDateTime >= B.StartDateTime
AND A.PrimaryKeyField <> B.PrimaryKeyField

If that gives you the wrong results try flipping the < and > signs. I
frequently get this confused.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================