How to set a "Gate" in access query

[apex77]

Here is my situation:
I am creating a database to calculate commissions for salespeople. Each
salesperson gets 4% commission until they reach what is called a gate (a
fixed amount). Once their commission has reached this gate amount, they get
8% of everything over the gate amount. I have tables called salesperson, gate
and sales.
Salesperson table just lists each salesperson
Gate lists the gate for each salesperson
Sales lists the amount sold.

I know this may be vague, but if anyone has an idea how to write a query
that calculates 4% of [sales]![totalinvoice] up to $6000 and everything over
$6000 is calculated at 8% that would be great. Thanks for the help.

 

[Jeff Boyce]

One approach might be to use an IIF() statement in your query. That might
look something like (untested):
IIF([Amt]<=6000, ([Amt] * .04), (6000 * .04) + (([Amt]-6000) * .08))

Another approach might be to create a function that calculates the value,
then use that function in your query.

Good luck!


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

The easy way to look at this is that the commission is actually 4% or total
sales + 4% of anything above the gate.

I would start out with a query that determines the sales for each
salesperson for each month (assuming that this applies on a monthly basis).
Something like:

SELECT SalesPersonID,
format(SalesDate, "yyyymm") as SalesMonth,
SalesSum(TotalInvoice) as CumSales
FROM Sales
WHERE Format(SalesDate, "yyyymm") = "200809"
GROUP BY SalesPersonID,
format(SalesDate, "yyyymm")

Then write another query that uses this as its source:

SELECT SalesPersonID, SalesMonth, CumSales,
(.04 * CumSales) +
(IIF([CumSales] <6000, 0, [CumSales]-6000) * .04) as Commission
FROM query1

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jerry Whittle]

In a query:

SELECT Sales.totalinvoice,

IIf([totalinvoice]<=6000,[totalinvoice]*0.04,([totalinvoice]-6000)*0.08+240)
AS Commission
FROM Sales;