Calculating Stepped Amounts

[Emma Hope]

I am trying to calculate commissions for sales people
which are stepped, i.e. say they generate $15,000 in a
month, the get commission as follows:

0 - 3000 0% (3000 * 0.00) 0
3001 - 10000 10% (7000 * 0.10) 700
10001 - 20000 20% (5000 * 0.20) 1000

total of $1700 paid in commission.

so somehow i need to put a query into place that breaks
the SumOfRevenue ($15,000) down into these steps....

Please can anyone help.
Thank you
Emma

 

[Gerald Stanley]

Try this IIf function to calculate the commission

IIf([sales]<3001,0,IIf([sales]<10001,([sales]-3000)*0.1,700+([sales]-10000)*0.2))


where [sales] holds the amount sold.

Hope This Helps
Gerald Stanley MCSD

 

[Michel Walsh]

Hi,


I assume the manager can decide to change the scale, or the percentage,
without having to touch and change your code, so, assume a table like:

SalesRates
FromThis ToThis AdditionnalRate
0 3000 0
3000 10000 .10
10000 20000 .10


note that I enter .10 in the range 10001-20000, not .20, because I enter an
ADDITIONNAL 10%, by comparison with the previous "scale".


If the table Sales has a field Sale, you write the query:
--------------

SELECT a.Salesman, LAST(a.Sale), SUM((a.Sale-b.FromThis) *
b.AdditionnalRate )
FROM Sales as a INNER JOIN SalesRates As b
ON a.Sales >= b.FromThis
GROUP BY a.SalesMan

---------------
As example, assume that John with 15000$. Before the aggregation, the join
will get us with the following data:

a.Salesman a.Sale b.FromThis b.AdditionnalRate
John 15000 0 0
John 15000 3000 .1
John 15000 10000 .1


so:

(a.Sale-b.FromThis)*b.AdditionnalRate
15000*0
12000*.1
5000*.1


and thus:

SUM( (a.Sale-b.FromThis)*b.AdditionnalRate) = 0+1200+500 = 1700$


Hoping it may help,
Vanderghast, Access MVP