Labor Cost for Date Range

[AnonY]

I'm designing a query that needs to figure out the labor cost for a certain time period based on hours worked. There is a regular pay rate and an OT pay rate. Right now I'm grouping on weeks (by week #'s). The query is based on start and end dates. If the date range starts in the middle of the week, it will not be able to sum up all the hours worked in that week, which will affect the labor cost of that week, since anything over 40 is multiplied by the OT pay rate.

Does anyone have any suggestions on how to get moving in the right direction on this?

Thanks,
DocX


--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-



x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 90,000 other groups
x-- Access to over 1 Terabyte per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD

 

[John Vinson]

I'm designing a query that needs to figure out the labor cost for a certain time period based on hours worked. There is a regular pay rate and an OT pay rate. Right now I'm grouping on weeks (by week #'s). The query is based on start and end dates. If the date range starts in the middle of the week, it will not be able to sum up all the hours worked in that week, which will affect the labor cost of that week, since anything over 40 is multiplied by the OT pay rate.

Does anyone have any suggestions on how to get moving in the right direction on this?

Don't store any grouping in tables at all, would be my recommendation.
At the least, store each day's hours in a separate record. You can
then create a Totals query to sum the hours. You can calculate the
total with overtime with an expression like

[PayRate] * IIF(Sum([Hours]) <= 40, Sum([Hours]), 40) + [OTRate] *
(IIF(Sum([Hours]) > 40, Sum([Hours]) - 40, 0)

John W. Vinson[MVP]

 

[rolaaus]

You may want to add a field into the table for the rate of pay. Some
companies will pay for any work done past 8 hours in a day (depending on if
your state hasn't changed the labor law - I think where I am in WA state, the
state changed the law to favor the companies so they only have to pay for
hours above 40 in a week, where I was in CA, they would pay above 8 hours in
a day). Anyways, store the hours work in a table, where the record is based
on the day worked, the amt of horus, then the "rate" (this is not the
employees hourly rate, since that should be stored in the employee table),
but rather, this would be 1 or 1.5 (time and a half) or even 2 (for double
time). Then you query can take the amt of hrs worked, times the employees
pay rate, times the "daily rate" - obviously this "daily rate" field would
have to be filled in my HR or the employees mngr, or you could automate its
entry in a forms "before update" event, if this app has a time card type
functionality.