Total Minutes Worked Within IVR Timing

[access_learner via AccessMonster.com]

Hi,

I need to figure out how many total minutes employee worked during the IVR
Timing.

Each employee works different shift and it can run overnight into next
morning where there may be IVR coverage before employee logs out. IVR Timing
is same for Saturday/Sunday and same from Monday to Friday.

IVR Timing Table; data type is Date/Time but only Time is available because
date should be same as the schedule date

DayIs IVRStart IVRStop
Sunday 10:00:00 AM 6:00:00 PM
Monday 9:00:00 AM 9:00:00 PM
Tuesday 9:00:00 AM 9:00:00 PM
Wednesday 9:00:00 AM 9:00:00 PM
Thursday 9:00:00 AM 9:00:00 PM
Friday 9:00:00 AM 9:00:00 PM
Saturday 10:00:00 AM 6:00:00 PM


Schedule Table; data type is date/time


Employee ShiftStart ShiftStop
1 09/04/2010 7:00:00 AM 09/04/2010 3:00:00 PM
2 09/04/2010 9:00:00 AM 09/04/2010 3:00:00 PM
3 09/04/2010 10:00:00 AM 09/04/2010 3:00:00 PM
4 09/04/2010 10:00:00 AM 09/04/2010 10:00:00 PM
5 09/04/2010 8:00:00 PM 10/04/2010 12:00:00 AM
6 09/04/2010 11:00:00 PM 10/04/2010 7:00:00 AM
7 09/04/2010 11:00:00 PM 10/04/2010 11:00:00 AM
8 10/04/2010 9:00:00 AM 10/04/2010 3:00:00 PM
9 10/04/2010 9:00:00 AM 10/04/2010 7:00:00 PM
10 10/04/2010 4:00:00 PM 10/04/2010 11:00:00 PM
11 10/04/2010 11:00:00 PM 11/04/2010 9:00:00 AM
12 10/04/2010 11:00:00 PM 11/04/2010 11:00:00 AM
13 10/04/2010 10:00:00 AM 10/04/2010 6:00:00 PM
14 10/04/2010 4:00:00 PM 11/04/2010 11:00:00 AM

Can someone please provide detailed solution using Access 2003 as how to get
the correct total minutes worked during the IVR Timing.

Thanks

Access Learner

 

[John W. Vinson]

Each employee works different shift and it can run overnight into next
morning where there may be IVR coverage before employee logs out. IVR Timing
is same for Saturday/Sunday and same from Monday to Friday.

IVR Timing Table; data type is Date/Time but only Time is available because
date should be same as the schedule date

But it ISN'T the same. You just said so. "it can run overnight into the next
morning" so the very nature of your data is that it is NOT the same as the
schedule date!

Consider storing the date and time together in the field, so that an IVR
coverage that starts at #4/9/2010 11:30pm# and ends at #4/10/2010 04:00am# can
be used with a very simple DateDiff expression to calculate the duration.

As it is, you'll need some complex and fallible logic to distinguish cases
that run over midnight from those that don't.