Time Tracking & Reports

[fireytech]

I need to make a report that will show me a sum of the total, regular,
and overtime hours of all employees for pay period "x". I know how to
set up a parameter for the underlying query to ask me which pay
period. I can generate the Total Hours sum easily but I don't know
how to get the report to calculate regular and overtime hours as a
total for all employees. The query/report would have to calculate the
individual employees overtime and then sum it because not all
employees work 40 hours a week so I can't just multiply 5 employees x
40 and subtract it from the total hours.

I keep all the time sheet entries in a table called
TBL_TimesheetTimes. There is one line for each in/out. The table has
fields called EmployeeID, PayPeriodEndDate, WeekEndDate, WorkDate,
TimeIN, TimeOut, & DailyTotalHours.

We have to calculate overtime on a weekly basis (if the employee works
79 hours for the pay period but 60 hours were in one week, we have to
pay 20 hours overtime).

 

[Dale Fye]

From the sounds of it, you may have some complicated overtime rules. I would
start out by developing a query that gives you the total hours for each
employee by pay period. Something like:

Query1:
SELECT EmployeeID, PayPeriodEndDate, WeekEndDate,
SUM(DailyTotalHours) as TotHours
FROM tbl_timesheeTimes
WHERE PayPeriodEndDate = #mm/dd/yyyy#
GROUP BY EmployeeID, PayPeriodEndDate, WeekEndDate

I would then develop another query to determine the number of regular and OT
hours per week for each employee. Something like:

Select EmployeeID, PayPeriodEndDate, WeekEndDate,
IIF([TotHours]< 40, [TotHours], 40) as TotReg,
IIF([TotHours]>40, [TotHours]-40, 0) TotOT
FROM query1
GROUP BY EmployeeID, PayPeriodEndDate, WeekEndDate

Then, you could create a third query to sum the TotReg and TotOT for the
payperiod.


fnRegHours would just return the portion of the hours <= 40 for a given week.
fnOTHours would return the portion of the hours over 40, for a given week.





--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.