time duration in access

cheryl9063
cheryl9063 used Ask the Experts™
on
I have a table for schedule adherence. In each record there is a repID, LogInType,scheduled time, actual time. These fields are formatted in time to see when a rep actual started the session and when it was scheduled. I want to sum the hours worked for any given day. There is no end time in each record. The end time is the next record which would have the next time in it.. See table below in code:
RepID	LogInType	ScheduledTime	Actual
4001	Open	8:00:00 AM	8:00:00 AM
4001	Break1	10:00:00 AM	10:05:00 AM
4001	Open	10:10:00 AM	10:15:00 AM
4001	Lunch	12:15:00 PM	12:15:00 PM
4001	Open	1:00:00 PM	1:15:00 PM
4001	Break2	3:00:00 PM	3:15:00 PM
4001	Open	3:15:00 PM	3:30:00 PM
4001	Logout	4:30:00 PM	5:00:00 PM
4001	LogIn	7:55:00 AM	7:55:00 AM

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
So wouldn't the total time for the day be the actual time of the last record of the day minus the actual time of the first record of the day?
cheryl9063

Author

Commented:
Yes that is true for one whole day but what if I wanted to calculate the time between record one and record 2? Is there a way to do it?

Thanks!
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<but what if I wanted to calculate the time between record one and record 2?>
Well that was not your orginal question...
thenelson has answered the "original" question.
;-)

Can you clearly state your *entire* question please?

If you are truly a beginner this may prove daunting.
To me it looks like you will need a recordset or a nested subquery.
So you will have to be fairly well versed VBA and /or SQL

In any event, this would be *much* easier if the end time was included with the start time.
Can you ask if this can be added?

JeffCoachman

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial