Calculating Overtime on report

[Bob Groger]

Hello,

I have a Timesheet application for figuring payroll. I need to calculate
overtime hours on a report. I have a Query that returns all the information
except the calculated information.

SQL for Query is:
SELECT Timesheet.Driver_ID, Employees.[Last Name], Employees.[First Name],
Timesheet.Terminal_ID, Timesheet.Date, Timesheet.Total_Paid_Time,
Timesheet.Training_Inst, Timesheet.Training_Stud, Timesheet.OP_VAC,
Timesheet.OP_HOL, Timesheet.OP_Other, Timesheet.OP_Reason,
Employees.Payroll_Company
FROM Employees LEFT JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between #12/4/2005# And #12/17/2005#) AND
((Employees.Payroll_Company)="RP"))
ORDER BY Timesheet.Driver_ID, Timesheet.Date;

I have a report based on this query grouped by driver and date by week.
Total_Paid_Hours includes Vacation, Holiday, and Other hours. I need to
calculate Overtime Pay per week in a text box as [Total_Paid_Time] -
[OP_Vac] - [OP_HOL] -[OP_Other] -40 but only if a positive number. I am no
math or syntax guru, and all attempts at this have returned inconsistent or
plain wrong results.

Can someone suggest the correct syntax to do this, or do I need to do the
calculations in the query?

Thanks,

Bob Groger

 

[Allen Browne]

It looks like you need to group by driver, to get the total values for the
week, and then see if the total comes to more than 40.

1. In query design view, depress the Total button on the toolbar.
Access adds a Total row to the design grid, and enter Group By under all
your fields.

2. Makes these changes in the Total row under your fields:
- Terminal_ID: remove from grid.
- Date: change to Where, and clear the Sorting.
- Total_Paid_Time: change to Sum
- Training_Inst: remove from grid
- Training_Stud: remove
- OP_Vac: change to Sum
- OP_Hol: change to Sum
- OP_Other: change to Sum
- OP_Reason: remove
- Payroll_Company: change to Where (unless you need it.)

3. Test the query results. You should see columns named SumOfOOP_Vac and so
on.

4. Back in query design view, type this expression into a fresh column in
the field row, and set it to Expression in the Total row:

OT: IIf([SumOfTotal_Paid_Time] > ([SumOfOP_Vac] + [SumOfOP_Hol] +
[SumOfOther]),
[SumOfTotal_Paid_Time] - [SumOfOP_Vac] - [SumOfOP_Hol] - [SumOfOther], 0)

 

[Bob Groger]

Allen,

Thank you for the help. I didn't even know the "Total" button was an option.
You have opened yet another door for me in this Access maze. I don't have it
working perfectly yet but I will keep at it.

Bob G.

It looks like you need to group by driver, to get the total values for the
week, and then see if the total comes to more than 40.

1. In query design view, depress the Total button on the toolbar.
Access adds a Total row to the design grid, and enter Group By under all
your fields.

2. Makes these changes in the Total row under your fields:
- Terminal_ID: remove from grid.
- Date: change to Where, and clear the Sorting.
- Total_Paid_Time: change to Sum
- Training_Inst: remove from grid
- Training_Stud: remove
- OP_Vac: change to Sum
- OP_Hol: change to Sum
- OP_Other: change to Sum
- OP_Reason: remove
- Payroll_Company: change to Where (unless you need it.)

3. Test the query results. You should see columns named SumOfOOP_Vac and
so on.

4. Back in query design view, type this expression into a fresh column in
the field row, and set it to Expression in the Total row:

OT: IIf([SumOfTotal_Paid_Time] > ([SumOfOP_Vac] + [SumOfOP_Hol] +
[SumOfOther]),
[SumOfTotal_Paid_Time] - [SumOfOP_Vac] - [SumOfOP_Hol] - [SumOfOther], 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a Timesheet application for figuring payroll. I need to calculate
overtime hours on a report. I have a Query that returns all the
information except the calculated information.

SQL for Query is:
SELECT Timesheet.Driver_ID, Employees.[Last Name], Employees.[First
Name], Timesheet.Terminal_ID, Timesheet.Date, Timesheet.Total_Paid_Time,
Timesheet.Training_Inst, Timesheet.Training_Stud, Timesheet.OP_VAC,
Timesheet.OP_HOL, Timesheet.OP_Other, Timesheet.OP_Reason,
Employees.Payroll_Company
FROM Employees LEFT JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between #12/4/2005# And #12/17/2005#) AND
((Employees.Payroll_Company)="RP"))
ORDER BY Timesheet.Driver_ID, Timesheet.Date;

I have a report based on this query grouped by driver and date by week.
Total_Paid_Hours includes Vacation, Holiday, and Other hours. I need to
calculate Overtime Pay per week in a text box as [Total_Paid_Time] -
[OP_Vac] - [OP_HOL] -[OP_Other] -40 but only if a positive number. I am
no math or syntax guru, and all attempts at this have returned
inconsistent or plain wrong results.

Can someone suggest the correct syntax to do this, or do I need to do the
calculations in the query?