Time Tracking & Reports

  • Thread starter Thread starter fireytech
  • Start date Start date
F

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).
 
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.
 
Back
Top