Time Tracking & Reports



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:

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

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.


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

email address is invalid
Please reply to newsgroup only.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question