Commission Salab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi every body
I have field NetSales and from there I want to Calculatie commission for
salesmen in my report . My commission crieteria is as follows:
Sales up 2500 - 0%
from 2501 to 5000 - 1%
From 5001 to 8000 - 2%
8001 and above - 3%
anyone can help me how I will do this?
Thanksin advance
Wahab
 
hi every body
I have field NetSales and from there I want to Calculatie commission for
salesmen in my report . My commission crieteria is as follows:
Sales up 2500 - 0%
from 2501 to 5000 - 1%
From 5001 to 8000 - 2%
8001 and above - 3%
anyone can help me how I will do this?
Click to expand...


Take a piece of paper and use a calculator.
or

SELECT Salesmann , iif(sumsales between 2501and 5000,sumsales*0.01,
iif(sumsales between 5000 and 8000,sumsales*0.02,
iif(sumsales > 8001,sumsales*0.03,0)))
FROM (
SELECT Salesman,Sum(Nz(NetSales)) as SumSales
From tSales
Group BY Salesman) as Q1

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
hi every body
I have field NetSales and from there I want to Calculatie commission for
salesmen in my report . My commission crieteria is as follows:
Sales up 2500 - 0%
from 2501 to 5000 - 1%
From 5001 to 8000 - 2%
8001 and above - 3%
anyone can help me how I will do this?
Thanksin advance
Wahab
Click to expand...

Try the following expression:

Commission: [Sales] * Switch([Sales] < 2500, 1, [Sales] < 5000, 1.01,
[Sales] < 8000, 1.02, True, 1.03)

The Switch() function takes arguments in pairs, and goes through them
left to right; when the first argument of the pair is True, it returns
the second argument of the pair and then quits.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I suggest a Commission table
SalesFrom SalesTo CommRate
0 2500 0
2500 5000 .01
5000 8000 .02
etc

This allows you to easily change the commission rate without ever having to
touch an expression in a query. If you don't want to drive this with data,
then consider creating a single function that accepts the Sales amount and
returns the commission rate. This would keep all your business rules in a
single place rather than in a query.
 
One other refinement to the table might be to have a column with the base amount
of commission. That would make life easier if the commission was 1% on the
amount of 2500 to 5000 and then 2% on the amount over 2500 and less than 5000.
Also, it can get tricky with numbers that are exactly on the demarcation if you
haven't structured your query precisely.

So I've sold 6500 worth of goods is my commission. $130 (.02*6500) or is it $55
((6500-5000) * .02) + ((5000-2500 * .01)) + (2500*0).
If the latter and I have a base amount, then I just need to add the base amount
to the remainder calculation.
 
Back
Top