Question How multiply different amount of hours by different amount of rates. Please Help. (1 Viewer)

[dsantosp12]

Hi,

I am trying to make a report in which I can see the amount of money that I have to pay weekly to an employee. To explain myself clearer, I have different rates for each employee. For example:

Name NormalPayRate VacationPayRate SickPayRate OvertimePayRate StandbyPayRate
John Smith $15 $10 $15 $30 $8
Ken Jonson $10 $8 $10 $20 $8

I am doing this in a query, if there is another way easier to do so, just let me know.
So what I want to be able to do is, for example, take 4 hours that the employee worked at NormalPayRate and multiply it. But if tomorrow the employee worked at a different rate, for example 3 hours overtime, I want those 3 hours multiplied by OvertimePayRate.

The way I saw it on my Google researches, is that they have only one rate and the amount will be [SumOfHours]*[Rate]. But how can I tell my query to skip, for example, one rate because the employee didn't work at that rate that day. So I can have different amount for the different rates.

By the way, entering the information I can divide at what rate the employee worked x day. So if the employee work 6 hours at regular, and later on 4 hours at standby, I have those information separated in my table.

Results that I am getting:
I can easily get the amount in money multiplying the whole hours by a rate, so my result in my report will be multiplied by the same rate.

Note: I am using Access 2013 to do so.

THANK YOU IN ADVANCED! I will appreciate any comments I really need to get tis done.

 

[EssexRich]

Hi there, keep the payrate fields in the employee table.

Then create a table for work rate levels. So, on your emplyee worklog table you can store the hours worked and the level they should be paid at.

 

[EssexRich]

Then you can calculate the totals.

Normally, id advise calculating totals on the fly and not storing them but in this case, I'd store them because of the likelihood of employees getting payrises which would screw up your old pay records if they were always calculated on the fly.