How to create tbl that calculate between 2 other tables?

[calvinle]

Hi,

I have 3 tables:
tblAmount: Contains all the items and the price for each items.
Item - Amount

tblCommission: Contains all employee name and the pourcentage commission for each employee.
EmployeeName - Percentage (Total 100%)

tblCalc: I created this one to input the result from the calculation of the 2 tables aboved.
Item - EmployeeName - CalcAmount

What I need:
If I click on a command button, it will calculate and the tblCalc will open and display for each item, the split for each employee.

Example:
tblAmount:
Scarf - 5$
Shoes - 10$
Jacket - 40$
GapItem - 21.13$

tblCommission:
Richard - 20%
Eric - 30%
Caroline - 35%
John - 15%

tblCalc result should be:
Scarf - Richard - 1$
Scarf - Eric - 1.50$
Scarf - Caroline - 1.75$
Scarf - John - 0.75$
Shoes - Richard - 2$
Shoes - Eric - 3$
Shoes - Caroline - 3.5$
Shoes - John - 1.5$
Jacket - Richard - 8$
Jacket - Eric - 12$
Jacket - Caroline - 14$
Jacket - John - 6$
GapItem- Richard - 4.23$
GapItem- Eric - 6.34$
GapItem- Caroline - 7.40$
GapItem- John - 3.17$

However, there is a gap if the GapItem because the amount is 21.13, but the total splitted is 21.14 which is over 1 penny. In the case that there are over or Under 1 penny, it will give to the first employee.

Can anyone help me on how to do it??

 

[calvinle]

I solved it

 

[calvinle]

can you explain how you solved it because i have a problem like you
thanks

I have used VBA to calculate the whole thing.

Dépends what you want to do..