Calculate regular and overtime

[topdesk123]

I have a database where time cards are tracked. Employees are paid certain amounts for certain type jobs. Often times it leads into overtime.

I've written a query that provides a running total for the week which works fine:

SELECT [Time Card Hours].TimeCardDetailID, [Time Card Hours].Employee, Sum([Time Card Hours].ActTime) AS SumOfActTime, (DSum("acttime","time card hours","[TimeCardDetailID]<=" & [TimeCardDetailID] & "")) AS RunTot
FROM [Time Card Hours]
GROUP BY [Time Card Hours].TimeCardDetailID, [Time Card Hours].Employee;

It provides this information:
Emp. ActTime RunTot
6 0.5 0.5
6 4.5 5
6 0.5 5.5
6 4 9.5
6 0.25 9.75
6 7.25 17
6 8.5 25.5
6 6.5 32
6 0.5 32.5
6 0.25 32.75
6 4 36.75
6 9 45.75
6 4 49.75
6 4.5 54.25

Most of my formulas work after this to calculate various RT and OT hours and pay rates, EXCEPT for example, the 3rd line from the bottom, where regular time and overtime have to be calculated from the same number.

When an employee hits 45.75 hours (for example) I need the actual time of that day, up to the 40 hour mark calculated at the regular pay rate - and then I need the balance of the actual time after 40 hours calculated at time and a half. Example:

John Smith
Thursday - Running Total=36.75 hours
Friday he works 9 hours - Running total = 45.75 hours

The first 3.25 hours get paid at $12.00/hr
The remaining 5.75 hours get paid at $18.00/hr.

Make more sense? Sometimes writing the question is harder than figuring out the solution!

THANKS!
topdesk123

 

[GingerR]

This depends a lot on how you have other queries and such written, based on this one.

Wouldn't you just subtract 40 from the final total? Why would you have to have it designate the 5.75 in that certain row?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[topdesk123]

Hi GingerR,

Thank you for your reply. The workers are paid various amounts depending on the type of job they are working on - so overtime is not just based on hours - it is based on the amount for the job they are on. Does that make sense?

Thanks!
topdesk123