Need Help with Counting of Sequence for worked hours

[ettekcor]

I have a query that has the following fields and data:

EmplID, EmployeeName, Job Code, Cost Center, InPunchDate InPunchTime
OutPunchDate,
235689 Mouse, Micky 58 RN 102326 09/24/2006
6:60:00AM 09/24/2006
235689 Mouse, Micky 58 RN 102326 09/25/2006
7:00:00AM 09/25/2006
235689 Mouse, Micky 58 RN 102326 09/26/2006
9:17:00AM 09/26/2006
235689 Mouse, Micky 58 RN 102326 09/29/2006
6:47:00AM 09/29/2006

OutPunchTime Hours
8:37:00pm 13.2833333333
1:00:00 am 17.5
11:36:00am 2.31666
7:42:00pm 12.41

I have over hundred records that I need to count any employee that
worked a 12 hours shifts for 3 consecutive days then I need to see if
they worked again within the next 24/36 hours.

For this I thought I could use DAYOFWEEK: DatePart("w",[OUTPUNCHDMY])
and then in antoher table I would put the following data Week1
09/24/2006-09/30/2006 and Week 2 10/01/2006-10/07/2006 to help count
work day. however, I still am not sure how I would count the
consecutive date worked.

Anyway, I am still searching for the right way to do this. Please
help!!!

 

[Allen Browne]

You will need some experience with queries to achieve this

The basic idea is to examine each record, and sum the number of minutes
worked by this employee between the start of this shift and the end of the
period you are concerned about. You can then determine whether they worked
more than the number of hours permitted in that period.

To simplify this example, I'm assuming you can combine InPunchDate and
InPunchTime into a single date/time field named InPunch; also OutPunchDate
and OutPunchTime into OutPunch.

You can interested in the timeframe between the start of the shift and 60
hours later (3 consecutive days, plus 36 hours after that), so the end of
the timeframe examine is:
DateAdd("h", 60, InPunch)

You want to sum the minutes for all shifts for this employee that start on
or after the shift you are examining, and before that time.

If the shift ends before that time, you want the whole of that time; if the
shift overlaps that time, you want just the minutes from that shift that are
within that timeframe. The IIf() expression below calculates that.

Since we are talking about multiple records from the same table, you need a
subquery to find and sum the values from those records, returning a total to
your main query. You can type the subquery into the Field row of your main
query, in query design. It will be something like this:

MinutesInNext60Hours: (SELECT Sum(DateDiff("n", Dupe.InPunch,
IIf(Dupe.OutPunch <= DateAdd("h", 60, Roster.InPunch),
Dupe.OutPunch,
DateAdd("h", 60, Roster.InPunch))) AS MinutesInPeriod
FROM Roster AS Dupe
WHERE (Dupe.EmpID = Roster.EmpID)
AND (Dupe.InPunch >= Roster.InPunch)
AND (Dupe.InPunch < DateAdd("h", 60, Roster.InPunch))

Notes:
====
1. Assumes the main table is named Roster.
The alias (Dupe) can be anything you like.

2. If you can't combine the InPunchDate and InPunchTime into one date/time
field, you will need to replace Roster.InPunch with:
CDate(Roster.InPunchDate + Roster.InPunch)
and similarly for OutPunch.

3. The basic logic for locating overlapping events is that:
A starts before B ends, and
B starts before A ends.

4. Access sometimes misunderstands the data type of calculated fields. You
might like to wrap the entire expression after the colon in:
CLng(Nz( ... ,0))


5. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that has the following fields and data:

EmplID, EmployeeName, Job Code, Cost Center, InPunchDate InPunchTime
OutPunchDate,
235689 Mouse, Micky 58 RN 102326 09/24/2006
6:60:00AM 09/24/2006
235689 Mouse, Micky 58 RN 102326 09/25/2006
7:00:00AM 09/25/2006
235689 Mouse, Micky 58 RN 102326 09/26/2006
9:17:00AM 09/26/2006
235689 Mouse, Micky 58 RN 102326 09/29/2006
6:47:00AM 09/29/2006

OutPunchTime Hours
8:37:00pm 13.2833333333
1:00:00 am 17.5
11:36:00am 2.31666
7:42:00pm 12.41

I have over hundred records that I need to count any employee that
worked a 12 hours shifts for 3 consecutive days then I need to see if
they worked again within the next 24/36 hours.

For this I thought I could use DAYOFWEEK: DatePart("w",[OUTPUNCHDMY])
and then in antoher table I would put the following data Week1
09/24/2006-09/30/2006 and Week 2 10/01/2006-10/07/2006 to help count
work day. however, I still am not sure how I would count the
consecutive date worked.

Anyway, I am still searching for the right way to do this. Please
help!!!