Determining an overlap in time between lines

Hi,
I am trying to determine if there is an overlap in time between tasks of
multiple employees. It does not make a difference if it is 2 different
employees or 2 different tasks, I am only interested if the end time overlaps
with the start time. For example:

Employee Task StartTime EndTime
1. Joe Receiving 01:00 01:41
2. Bob Loading 01:10 01:20
3. Sue Shipping 01:20 01:28

Line 1 and Line 2 - end time of line 1 overlaps with start time of line 2.
Is there a way of flagging this in my report? Thanks for your help.
--
clueless

SELECT Employee, Task, StartTime, EndTime
, Exists (SELECT * FROM SomeTable As Temp
WHERE Temp.StartTime SomeTable.StartTime
AND Temp.StartTime SomeTable.EndTime)
FROM SomeTable

That should return TRUE for line 2. If you want true for both line 1 and 2
then the criteria becomes more complex.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

clueless wrote:
Hi,
I am trying to determine if there is an overlap in time between tasks of
multiple employees. It does not make a difference if it is 2 different
employees or 2 different tasks, I am only interested if the end time overlaps
with the start time. For example:

Employee Task StartTime EndTime
1. Joe Receiving 01:00 01:41
2. Bob Loading 01:10 01:20
3. Sue Shipping 01:20 01:28

Line 1 and Line 2 - end time of line 1 overlaps with start time of line 2.
Is there a way of flagging this in my report? Thanks for your help.

Thank You!!!! Perfect! Have a great day.
--
clueless


"John Spencer" wrote:

SELECT Employee, Task, StartTime, EndTime
, Exists (SELECT * FROM SomeTable As Temp
WHERE Temp.StartTime SomeTable.StartTime
AND Temp.StartTime SomeTable.EndTime)
FROM SomeTable

That should return TRUE for line 2. If you want true for both line 1 and 2
then the criteria becomes more complex.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

clueless wrote:
Hi,
I am trying to determine if there is an overlap in time between tasks of
multiple employees. It does not make a difference if it is 2 different
employees or 2 different tasks, I am only interested if the end time overlaps
with the start time. For example:

Employee Task StartTime EndTime
1. Joe Receiving 01:00 01:41
2. Bob Loading 01:10 01:20
3. Sue Shipping 01:20 01:28

Line 1 and Line 2 - end time of line 1 overlaps with start time of line 2.
Is there a way of flagging this in my report? Thanks for your help.

Hi John,
Sorry to bother you but I just notice something on my report. I have it
showing "yes" if it overlaps and out of 74, 1 should and does not.

Employee Task StartTime EndTime Overlap
1. X Loading 16:10 16:30 Yes
2. A Receiving 16:14 16:30
3. F Shipping 16:30 16:40
4. J Picking 16:30 16:50
5. P Loading 16:50 17:25

Line 3 should show "yes" but it doesn't. Any suggestions on how to correct
this.
Thanks again for all your help,
--
clueless


"John Spencer" wrote:

SELECT Employee, Task, StartTime, EndTime
, Exists (SELECT * FROM SomeTable As Temp
WHERE Temp.StartTime SomeTable.StartTime
AND Temp.StartTime SomeTable.EndTime)
FROM SomeTable

That should return TRUE for line 2. If you want true for both line 1 and 2
then the criteria becomes more complex.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

clueless wrote:
Hi,
I am trying to determine if there is an overlap in time between tasks of
multiple employees. It does not make a difference if it is 2 different
employees or 2 different tasks, I am only interested if the end time overlaps
with the start time. For example:

Employee Task StartTime EndTime
1. Joe Receiving 01:00 01:41
2. Bob Loading 01:10 01:20
3. Sue Shipping 01:20 01:28

Line 1 and Line 2 - end time of line 1 overlaps with start time of line 2.
Is there a way of flagging this in my report? Thanks for your help.