Calculating Overtime Problem

[`Tis Me]

I have run into a problem calculating overtime. My Timesheet DB has
several years worth of records for all employees. However, our
overtime is not based on a 40 hour week. It is based on a 28 day
cycle. Anything over 171 hours in 28 days is overtime. I have tried
everything I can think of to break up the date ranges into 28 day
increments from HireDate and calculate the total hours in those ranges
from HireDate, but cannot get it to work correctly. Does anyone have
an idea how to start from the HireDate value, add 28 days, total the
hours in those 28 days and if the total is greater than 171 show the
amount of overtime? My table structures are as follows:

Tbl_Employee:
EmpID - Key
Name: - Employee Name
HireDate - Date Employee Was Hired

Tbl_Time:
TimeID - Key
EmpID - From Employee Table
DTIn - Date & Time In
DTOut - Date & Time Out

I'm not a newbie to Access and can build the rest of the queries to
subtract the amount used, I just can't get the 28 day cycle figured
out. Any help would be greatly appreciated!

TIA

Frank

 

[Allen Browne]

1. Create a query, and add both tables.

2. Depress the Total button on the Toolbar.
Access adds a Total row to the grid.

3. Drag the EmployeeID field from tbl_Employee into the grid.
Accept Group By in the Total row under this field.

4. Type this expression into a fresh column in the Field row:
PayPeriod: DateDiff("d", [tblEmployee].[HireDate], [tbl_Time].[DtIn]) \
28
Accept Group By in the total row under this field.

This field calculates the number of days between the shift and when the
person was hired, and performs integer division by 28 to break them into
28-day periods. It then groups by this period.

5. In the next column in the Field row, enter:
Hours: DateDiff("n", [tbl_Time].[DTIn], [tbl_Time].[DTOut]) / 60
Choose Sum in the Group By field under this field.

This field calculates the number of minutes worked in each shift and divides
by 60 to get hours and fractions, since DateDiff() would hours would not
include the minutes. It then sums all the shifts in the period for the
employee.

6. In the next column, in the Field row, enter:
OverTime: 171 - [Hours]
In the Total row under this field, choose Expression.

If you need criteria under the OverTime field, you will have to combine the
fields from steps 5 and 6 into a single field. To do that, switch the query
to SQL view (View menu), and replace both fields with:
171 - Sum(DateDiff("n", [tbl_Time].[DTIn], [tbl_Time].[DTOut]) / 60 AS
OverTime

This approach calculates the overtime based only on the date the shift
*started*. For example if someone started on 1/1/06, and on Jan 28th they
started an 8 hour shift at 9pm, it counts the whole 8 hours as part of their
first 28-day period, even though 5 of the hours strictly occurred on a day
that was in their 2nd pay period.

 

[`Tis Me]

Thanks Allen,

This worked perfectly!

Frank

On Sat, 21 Jan 2006 15:46:45 +0800, "Allen Browne"

1. Create a query, and add both tables.

2. Depress the Total button on the Toolbar.
Access adds a Total row to the grid.

3. Drag the EmployeeID field from tbl_Employee into the grid.
Accept Group By in the Total row under this field.

4. Type this expression into a fresh column in the Field row:
PayPeriod: DateDiff("d", [tblEmployee].[HireDate],
[tbl_Time].[DtIn]) \
28
Accept Group By in the total row under this field.

This field calculates the number of days between the shift and when
the
person was hired, and performs integer division by 28 to break them
into
28-day periods. It then groups by this period.

5. In the next column in the Field row, enter:
Hours: DateDiff("n", [tbl_Time].[DTIn], [tbl_Time].[DTOut]) / 60
Choose Sum in the Group By field under this field.

This field calculates the number of minutes worked in each shift and
divides
by 60 to get hours and fractions, since DateDiff() would hours would
not
include the minutes. It then sums all the shifts in the period for the
employee.

6. In the next column, in the Field row, enter:
OverTime: 171 - [Hours]
In the Total row under this field, choose Expression.

If you need criteria under the OverTime field, you will have to
combine the
fields from steps 5 and 6 into a single field. To do that, switch the
query
to SQL view (View menu), and replace both fields with:
171 - Sum(DateDiff("n", [tbl_Time].[DTIn], [tbl_Time].[DTOut]) /
60 AS
OverTime

This approach calculates the overtime based only on the date the shift
*started*. For example if someone started on 1/1/06, and on Jan 28th
they
started an 8 hour shift at 9pm, it counts the whole 8 hours as part of
their
first 28-day period, even though 5 of the hours strictly occurred on a
day
that was in their 2nd pay period.