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?