Calculate Hours worked in a Shift

[softwarechick]

I am currently working on a Database in which I am trying to find out how many employee hours were spent during a specific shift (ie: 7-3, 3-11, & 11-7), however, not everyone works the same times, some employees are 12 hours some 10, 8 or even cont. employees that may clock in and out several times a day. I have created a query that I can tell which shift they clock in on, and I know the total time they were here, but that does not help with breaking it up in how many hours were spent on each shift. Does anyone have any ideas, or know of an Access Program that already exists that will do this?

 

[MichaelRed]

hmmmmmmmmmmmmm,

several 'issues' here.


1[tab]there is nertainly no intrinsic function for it
2[tab]i doubt there is any readily available user developed function for it
3[tab]given the specifics noted in your post it cannot be done.
4[tab]given the actual start and end timed (as date & time), and a working definition as to how to assign the time worked to a shift, a simple query (or so) would easily return some results

I mention 'working definition' of how to assign the the time as your post appears to have a conflict in this, so it is not clear (at leaset to me) wheather the time is assigned to the shift which included the start time, or the actual shift definition time. Further, even after this is 'understood', some issue will remain.

For instance, ASSUMING the hours are assigned to the shift where the start time is defined, and if BodyPart1 "Logs in" at 6:45 for the 7-3 shift is the fifteen minutes assigned to the 11-7 shift and the remainder to the 7-3? Or are all hours assigned to the 7-3 ... ad-nausem ... at least until these are understood


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[softwarechick]

Thank you for your response, to help clear up a few things, I have imported an ODBC Table into Access, the fields that are included in my table are [punchin](the actual time the employee clocked in, [punchout](actual time the employee clocked out,[calculatedtime](time the employee spent clocked in during that time), these are time fields that I can run calculations on, however my goal is to see how many hours the employee work during a shift. ie: an employee works a 12 hour day, 6:00 a.m. till 6:00 p.m., they would have 1 hour in the 11-7 shift, 8 hours in the 7-3 shift and 3 hours in the 3-11 shift. My report will actuall me, how many staff hours did I have on the 7-3 shift, etc. We are a healthcare facility and the goal is to try and find our staff to patient ratio during each shift.

I hope this clears it up for you a little.

Kelly Snoke
kellys@fmchealth.org

 

[MichaelRed]

but are they DATE-Time fields or just time fields?

If they are DATE-Time, the seperation into BUCKETS is easily done. Without the date part, you can't really do it. You could add the date part from other info.

The process would simply allocate the time for each shift to a seperate record (probably a seperate table). Once in that form. just sum the time re each shift / date.

Send me (e.mail) a sample log table as you describe, and I'll do at least a brief sample and return it to you.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places