[AccessD] (no subject)

Do NOT hard code the values in code.  Build a table of From/To/Commission.
This will allow you to tweak your commissions as desired by changing values
in the table.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Sunday, May 22, 2005 11:12 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com