How multiply different amount of hours by different amount of rates. Please Help.

[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.

[JoeM]

What does your table that shows the employee, number of hours they worked, and the "rate" those are (Normal, Vacation, Overtime, etc) look like?
If you could post a small sample of that, that would be helpful in coming up with a solution.

[dsantosp12]

Thanks JoeM for the response.

Here is the pic of my employees table.


Thank you again.

[JoeM]

No, I meant your other table. Your initial post of the rates table was fine.
I am asking about your other data table, the one that houses the hours for each person that you want to multiply these rates by.

[burrina]

You really should have a separate table for Pay Rates. I did not see an Effective Pay Rate Date? That said, you can add fields to your qry like as an example:
Employees Net Pay Minus any Deductions.

ExpNetPay: [SumOfExpTotPay]-[ExpTotDed]

HTH

[dsantosp12]

But the rates change for every employees. For example, if you check the picture with the rate information per employee, the NormalPayRate of Julie Hart is no the same as the Elaine Drolet. Should I create one table for each rate of each employee? Sorry if I am not getting it or I didn't explained myself clear.

[dsantosp12]

No, I meant your other table. Your initial post of the rates table was fine.
I am asking about your other data table, the one that houses the hours for each person that you want to multiply these rates by.

OK here its a portion of the table where the hours are inputed.



PD: Sorry for the Mute pic in the middle.

Thank you again.

[burrina]

I need to point out, Not being critical that you have Bad Characters in your Category field. +$ are not acceptable naming conventions.
NO, you do NOT have to have a separate table for each Employees Hourly Rate. I am NOT familiar with your Schema but queries and expressions are what is going to get you the end results you are wanting and or DLookups.

Depending on how the time is actually entered is the key here. I will post a couple of screenshots of VERY old db's that may help.


HTH

[JoeM]

But the rates change for every employees.

That is why we have a query that joins the two tables on the EmployeeID field.

The simplest way to do this is to ensure that our tables are structured properly. Your Hours table is structured fine, but your Rates table would be structured better if the fields were this:

EmployeeID
EmployeeName
RateCategory
Rate

So, for instead of having one record per employee with 5 rate fields, you would have (up to) 5 records per employee, one for each rate.

Then, all you would have to do in your query is join your two tables on the EmployeeID and Category fields, and multiply the Rate from your Rates table by the Hours in your Hour table. If you want the totals by day, you would then simply make this an Aggregate Query which Groups by EmployeeID and WorkDate.

Does that make sense?

[June7]

Personally, I think building accounting db from scratch is path to insanity, especially if payroll is involved because of labor and tax laws. Why reinvent the wheel? Consider OTS software like QuickBooks.

[burrina]

AMEN ! Color me [UNSUBSCRIBED]

Good Luck With Your Project!

[orange]

Just a comment to say I agree with June7 --you'll be happier with a supported/designed product with users and forums such as QuickBooks.

Your pay rate by individual only serves to complicate, in my view. Often you have job classes or pay grades that an employee is assigned. This appoach can be maintained and can provide a simple means for employees to change jobs(pay grades) without additional complexity.

At least consider an OTS package.
Good luck.

[dsantosp12]

Hi guys thanks all for the responses. June7 I talked to my boss about QuickBook and he is trying to get it. Appreciate all of you, and your time.

Thank you again.