Need Query Formula to Remove OT from Timesheets

[kagoodwin13]

My question is less of an Access development question, but more of an open question to figure out how to write a formula. Please let me know if this is in the wrong section or forum.

I am developing a job cost tracking database to calculate costs spent per task by month. I am trying to figure out how to handle costing for salaried employees, to remove overtime hours, which would falsely inflate costs.

I have a table storing time sheets (tblTIME_SHEETS).
In this table are the fields Timesheet_ID (primary key), Timesheet_Date (mm/dd/yyyy), Employee_Name, Task_Name, and Time_Hours (1-24).

I have another table storing Salary info.
In that table are the fields Employee_Name, Salary_Class (H or S), and Hourly_Rate (currency)

The current process is being calculated in Excel:
If Salary Class = S, Timesheet_ID_Cost = Hourly_Rate * (160 Hours in Month / Sum of Hours Worked in Month). This removes OT pay for salaried employees.
If Salary Class = H, Timesheet_ID_Cost = Hourly_Rate. Hourly employees do not have OT pay removed.



My current dilemma is how to remove OT pay per line, without making a separate query or calculation for each month. It seems to me that it is an unsustainable method of managing this process. Is there an easier or neater way to remove OT pay from the salary employee's hourly pay?

[orange]

Please show your tables(expanded) and relationships. You may have an issue at this level.

It sounds like you are multiplying Hours * HourlyRate for all Employees. You should only do this for HourlyEmployees.
Since OT only applies to HourlyEmployees, calculation should not include SalariedEmployees. If you don't calculate OT for SalariedEmployees , you won't have to remove it.

[kagoodwin13]

orange,

Thanks for your quick reply. I have attached a spreadsheet to show an example of the current process. The example is a one-week sample.

Time Sheet Example.zip

Tabs are:
1. tblTIME_SHEETS (Timesheet_ID PK)
2. tblSALARY (Employee_Name PK)
3. qryADJ_HOURLY_RATE: Sum of Time_Hours, group by Employee_Name. If Salary_Class = S, then Adj_Hourly_Rate = Hourly_Rate * (40/Sum of Time_Hours). This lowers the hourly rate to adjust for hours worked over 40 in this week.
4. qryCOSTING: Adj_Hourly_Rate x Time_Hours = Timesheet_ID_Cost
5. rptTIMESHEET_ID_COST: Sum of Timesheet_ID_Cost, group by Task_Name. This is the desired output.

I like your suggestion to only multiply hours for hourly employees. However the data for hourly and salary employees is recorded in the same manner. I am wondering if that requires a separate calculation like I have attempted.

For salary employees, I can also get their annual salary from HR, rather than using their hourly rate, if that would help.

Thanks in advance for any suggestions you can offer.

[rpeare]

in your sum of time_hours field instead of doing a strict sum could you not have a formula like:

iif(salary_class = "S" and sum(time_hours) > 40, 40, sum(time_hours))

then all your multiplication would work out fine from the maximum of 40 billable hours per person that is salaried.

[kagoodwin13]

Mathematically, I must make a bucket of hours to compare a group-sum of hours, otherwise I cannot compute how much overtime to compensate for.

I'll have to make a month-by-month set of queries. I was hoping to avoid that, but it doesn't look like I have an option.

Thanks all for your help. I'm going to mark this as solved.

[rpeare]

I do not understand your use of verbiage.

Perhaps give an example of the raw data (I think I can glean that from tblTime_Sheets and tblSalary)

Your calculation, however, unless you are going to accept a level of inaccuracy will not work as you stated it.

There are not always 160 working hours in a month for instance in June of 2014 for a salaried person there would be 168 billable hours not 160. It also doesn't take into account if the person is salaried and worked 1 hour on the project. your formula would effectively be their hourly rate * 160 which would mean kenny mccormick would end up with an adjusted hourly rate of 7200.

If you want to stick with your formula and show the results reflected in qryAdj_Hourly_Rate it would be:

If Salary Class = S, Timesheet_ID_Cost = Hourly_Rate * (160 Hours in Month / Sum of Hours Worked in Month). This removes OT pay for salaried employees.
If Salary Class = H, Timesheet_ID_Cost = Hourly_Rate. Hourly employees do not have OT pay removed.

[hourly_rate] * iif([salary_class] = "H", 1, 160/(Sum([Time_Hours]))