[AccessD] (no subject)

On 22 May 2005 at 19:46, Res-Com Environmental wrote:

> I am trying to build a commission system for our sales employees in Access.
> We are currently using tables for customer, orders, employees, products etc.
> to link new orders to certain employees, but we want to setup a commission
> pay system so that employees will receive a certain percentage of each sale
> based on their number of sales (i.e. if they ge 3-8 sales they get 10%, 8-10
> is 13%, 10-12 is 17%, etc., etc. I am relatively new to Access and am

Presumably that should be something like 3-7,8-10,11-12 etc (otherwise you 
have two different answers for 8 sales per week)

> wondering if anyone has any advice on how I should start. The system would
> have to total an employee's weekly sales numbers, produce their percentage
> commission based upon that, total their sales revenue, and then calculate
> the total commission they would receive. This may be a simple question, but
> I've been having a lot of trouble with it. Any help would be greatly
> appreciated.
> 

Create a function in a module:

Function Commission(Sales As Long) As Double
Select Case sales
    Case Is < 3
        commission = 0
    Case 3 To 7
        commission = 0.1
    Case 8 To 10
        commission = 0.13
    Case Is > 10
        commission = 0.17
End Select
End Function


Assuming you have a tblOrders contains values 
OrderNum,Salesman,OrderDate,Value, the following query will give you your 
commission:

SELECT Salesman, Count(OrderNum) AS Orders, Sum(Value) AS TotValue, 
Commission([Orders]) AS CommissionRate, [TotValue]*[CommissionRate] AS 
CommissionValue
FROM tblOrders
WHERE (((OrderDate) Between [Startdate] And [Endate]))
GROUP BY Salesman;

-- 
Stuart