I need help understanding how to get a query to calculate an employee's sales (tblSales) and calculate and expression (Commission) based on a commission table (tblCom) that corresponds to that employee's sales.
tblSales: employeeID, sales,
tblcom: comStart, comEnd, comFactor, comRate
In tblSales are all individual sales for each employee e.g.
employeeID = 1, sales = 2,
employeeID = 1, sales = 5,
employeeID = 1, sales = 7,
employeeID = 2, sales = 9,
employeeID = 2, sales = 15,
employeeID = 2, sales = 12,
In tblcom are the commission structure e.g.
comStart = 1.00, commend = 10.00, comFactor = 0.10, comRate = 10%,
comStart = 11.00, commend = 20.00, comFactor = 0.20, comRate = 20%,
comStart = 21.00, commend = 30.00, comFactor = 0.30, comRate = 30%,
comStart = 31.00, commend = 40.00, comFactor = 0.40, comRate = 40%,
Query should return:
employeeID TotalSales TotalCom ComRate
1 14 2.8 20%
2 36 14.4 40%
Any help or suggestion is greatly appreciated