Totalling Payroll Hours by Employee

Question

Okay, we have an Access database in which we record post production, non production and other hours each day for each employee. Employees may have days where their hours are split between projects.

For Payroll entry purposes, we have designed a report that totals the hours worked and calculates the regular time and overtime hours. The issue I am having is that if the day of work is split between two projects then it calculates the hours for that date wrong.

For example, on July 10 the employee worked a total of 11 hours. Of those hours, 5 were spend on WA302 and 6 were spent on HU047. When I generate the report for the payroll hours it gives me totals for the two amounts seperately which means the overtime hours will not be calculated properly.

How do I get it to total the hours based on the date for each day worked?

This is the SQL statement:

SELECT
    EmployeeLookUptbl.EmployeeDriver,
    EquipmentTimesheettbl.Employee,
    EquipmentTimesheettbl.Date,
    EquipmentTimesheettbl.ProductionHours,
    EquipmentTimesheettbl.NonProductionHours,
    EquipmentTimesheettbl.RepairHours,
    EquipmentTimesheettbl.[Stat Holiday],
    EquipmentTimesheettbl.TravelTime,
    EquipmentTimesheettbl.TruckWash,
    EquipmentTimesheettbl.FirstAid,
    EquipmentTimesheettbl.DesignatedDriver,
    EquipmentTimesheettbl.LOA,
    EquipmentTimesheettbl.[Vehicle Allownance],
    EquipmentTimesheettbl.DaysWorked,
    [ProductionHours]+[NonProductionHours]+[RepairHours] AS TotalHours,
    IIf([TotalHours]>8,8,[TotalHours]) AS RegularHours,
    IIf([totalhours]<8,0,[totalhours]-8) AS Overtime,
    EquipmentTimesheettbl.RPP,
    EmployeeLookUptbl.FirstName,
    EquipmentTimesheettbl.Phase
FROM
    EquipmentTimesheettbl
    LEFT JOIN EmployeeLookUptbl
    ON EquipmentTimesheettbl.Employee = EmployeeLookUptbl.ReportName
WHERE
    (((EquipmentTimesheettbl.Date)
    Between [Enter Start Date] And [Enter End Date]))
ORDER BY EquipmentTimesheettbl.Date;

Answer 1

hours should be a property of the job so you can access it like so:

Total.hours = Job.hours + Job2.hours;

translates to

Total.hours = WA302.hours + HU047.hours;

translates to

Total.hours = 6+5;

Hope that helps your thought process, but unless you show us how you are doing it currently (SQL, Java, etc) we can't help you other then thought process.

Edit: from SQL,

sum(hours)

and each job should be its own row.

Answer 2

You need to use SUM() to provide a single total of hours. Also, simplify the query -- remove all the fields you don't need.

Start with this:

SELECT Equip.Employee,
    Sum([ProductionHours]) AS TotalProductionHours, 
    Sum([NonProductionHours]) AS TotalNonProductionHours, 
    Sum([RepairHours]) AS TotalRepairHours, 
FROM EquipmentTimesheettbl As Equip
    LEFT JOIN EmployeeLookUptbl As Employee 
    ON Equip.Employee = Employee.ReportName
WHERE
    (((Equip.Date)
    Between [Enter Start Date] And [Enter End Date]))
ORDER BY Equip.Date;

Then use it as the basis for a new query. You can make this one above even more sparse -- save your JOINS for later, if the information they provide are just for the report.

Wherever you see separate lines instead of a total, either remove the field that is forcing the breakout OR insert a GROUP BY clause. 'Date' is a good example of a field that belongs in the WHERE clause but NOT in the 'SELECT' clause.

Here is a tutorial that may help. Also, Access Help should answer this for you (although just now I didn't find quite what I wanted about this topic).

Also, it is very important you not name a table field "Date" as that is an Access reserved keyword!