We pay our sales team a commission. The first sale in a new account they get a higher %, and then incrementally lower % until sale 3+
I have a query that counts the number of orders for each store, but if they make a first and second sale within the same pay-period, I want to be able to tell which is first and which is second so that they get the correct % for each order.
And I would like to be able to look back at previous pay-period reports and see the amount they were paid for their first few sales in an account even after there are 3+ sales.
What I have so far is two queries, the first counts the orders per store after 1/1/2017:
qrySalesCount
Code:
SELECT tblRetailersActive.RetailID, Count(tblRetailersActive.RetailID) AS CountOfRetailID, tblOrderConf.IsSample, tblRetailersActive.PrivDesc
FROM tblRetailersActive INNER JOIN tblOrderConf ON tblRetailersActive.RetailID = tblOrderConf.RetailerID
WHERE (((tblOrderConf.DelDate)>#1/1/2017#))
GROUP BY tblRetailersActive.RetailID, tblOrderConf.IsSample, tblRetailersActive.PrivDesc
HAVING (((tblOrderConf.IsSample)=No) AND ((tblRetailersActive.PrivDesc) Not Like "*Producer*" And (tblRetailersActive.PrivDesc) Not Like "*processor*"));
The second calculates the commission amount based on the number of orders per store:
qryComAmt
Code:
SELECT tblRetailersActive.Tradename, tblOrderConf.Comtbl, tblEmployees.FirstN, tblEmployees.LastN, qrySalesCount.CountOfRetailID, IIf([CountOfRetailID]=1,0.2,IIf([CountOfRetailID]=2,0.1,0.05)) AS ComAmt
FROM (tblRetailersActive INNER JOIN qrySalesCount ON tblRetailersActive.RetailID = qrySalesCount.RetailID) INNER JOIN ((tblOrderConf INNER JOIN tblOrderDetails ON (tblOrderConf.ConfTableID = tblOrderDetails.ConfTableID) AND (tblOrderConf.ConfTableID = tblOrderDetails.ConfTableID)) INNER JOIN tblEmployees ON tblOrderConf.ComEntorEmp = tblEmployees.Employee) ON tblRetailersActive.RetailID = tblOrderConf.RetailerID
GROUP BY tblRetailersActive.Tradename, tblOrderConf.Comtbl, tblEmployees.FirstN, tblEmployees.LastN, qrySalesCount.CountOfRetailID;
As a test I made sure to attribute the commission to one store with only 1 order, and the 2nd order of another store with 8 orders. As is, it only counts commission based on the total number of orders.
Thanks for any help I can get!