x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 2875

Here's a challenge,

I have a table of sales reps, (name and ID)
I have a table of sales reps graduated commission levels (ID, Level, Rate) -
ie.
Jane Smith, \$0, 1%
Jane Smith, \$100,000, 2%;
Jane Smith, \$250,000, 3%;
Jane Smith, \$500,000, 5%

I have a table of sales transactions.

The query I'm trying to create is to calculate the sales rep commission for the month.  If Jane Smith sells \$350,000 for the month, the first \$100,000 @ 1% is \$1,000 plus the next \$150,000 is @ 2% for \$3,000 plus the last \$100,000 is @ 3% for \$3,000 for total commisions of \$7,000 for the month

I am pretty sure I can solve this with VBA, but I'd rather a query solution if anyone out there has any ideas.

Thanks!
0
dcaparaso
• 11
• 6
• 3
• +1
1 Solution

Commented:
Sure, you need a set of nested iif() statements in a calulated field..

Assuming the sum of sales is called sumSales...

commission: IIf([sumSales]>500000,[sumSales]*0.5,IIf([sumSales]>250000,[sumSales]* 0.3,IIf([sumSales]>100000,[sumSales]* 0.2,[sumSales]* 0.1)))
0

Author Commented:
Except each salesperson would have different levels and rates at each level
0

Commented:
It can be done with a combination of IIF, DLOOKUP, NZ and a calculation for all positive remainders of the part for that percentage, but:
1) the number of ranges needs to have a max number as the query van't deal with "dynamically" added field.
2) time must be of no concern as it will be sloooooow ;-)

Personally I would go for the VBA approach as that's not requiring a max number of ranges and will perform much better.

Need help with that ?

Nic;o)
0

Author Commented:
Nico,

What are your thoughts on the VBA solution, obviously it would be a function, something that could be called from a query.

Dave
0

Commented:
Yes Dave,

A function something like:

fncCommission(strYearMonth,ID)

The strYearMonth to get the salesamount for the ID and the ID also to get the ranges needed.

You'll need two recordsets:
1) Sales
2) SalesPersonRanges

When you have the salesamount from (1) you need a WHILE loop on recordset (2) with a WHILE salesamount > range value or EndOfFile.

Getting the idea ?

Nic;o)
0

Author Commented:
Nico,

What are your thoughts on the VBA solution, obviously it would be a function, something that could be called from a query.

Dave
0

Author Commented:
Nico,

Got the sales amount from the sales table, but I'm blanking out on the loop.  Here's where I'm at

Function SalesCommission(employee_id As Integer, month As Integer, year As Integer) As Currency

Dim SalesLevel As Currency
Dim Commission As Currency

With rst_sales_amt
.Filter = "[employee_id] = " & employee_id & _
" AND [month] = " & month & _
" AND [year] = " & year
SalesLevel = rst_sales_amt![Sales_Amt]
End With

Do Until SalesLevel = 0 <-- this may not be the best way

Loop

SalesCommission = Commission

End Function
0

Author Commented:
Nico,

Got the sales amount from the sales table, but I'm blanking out on the loop.  Here's where I'm at

Function SalesCommission(employee_id As Integer, month As Integer, year As Integer) As Currency

Dim SalesLevel As Currency
Dim Commission As Currency

With rst_sales_amt
.Filter = "[employee_id] = " & employee_id & _
" AND [month] = " & month & _
" AND [year] = " & year
SalesLevel = rst_sales_amt![Sales_Amt]
End With

Do Until SalesLevel = 0 <-- this may not be the best way

Loop

SalesCommission = Commission

End Function
0

Commented:
This piece is indeed a problem:

>Do Until SalesLevel = 0 <-- this may not be the best way
>
>Loop

There are two constraints:
1) Salesamount less then max for the range.
2) EOF on the ranges.

And you need the percentage of the "previous" row.
And store the percentage e.g. in SavePercentage
The start the WHILE NOT rst_commission_structure.EOF loop with a .movenext.
Now the commission for the first (saved) percentage can be calculated over the amount less then the read amount.

Getting the idea?

Nic;o)
0

Author Commented:
ADO is not needed, its become habit for me, working on both SQL projects and Access.

I suppose a sort order would be required, else the levels may not be read correctly?
0

Author Commented:
ADO is not needed, its become habit for me, working on both SQL projects and Access.

I suppose a sort order would be required, else the levels may not be read correctly?
0

Commented:
Correct !

OrderBy ID, Level

will do !

Nic;o)

0

Commented:
Actually, if you sort the commission levels as highest dollar limit (and percentage) to lowest you could get away with the following logic for the loop:

with rst_commission_structure
Do Until .eof

if rst_commission_structure![level] > SalesLevel then
Commission = Commission _
+ (SalesLevel - rst_commission_structure![level]) _
* rst_commission_structure![rate]
SalesLevel = rst_commission_structure![level]
endif

.movenext
Loop
end with
0

Commented:
Oops, the if line should be:

if SalesLevel > rst_commission_structure![level] then

0

Commented:
Nevaar,

I would reduce the SalesLevel like:
if rst_commission_structure![level] > SalesLevel then
Commission = Commission _
+ (SalesLevel - rst_commission_structure![level]) _
* rst_commission_structure![rate]
SalesLevel = SalesLevel - rst_commission_structure![level]
endif

And this way always all rows need to be processed, even for e.g. 5000 \$, so when only the level 1 range is needed.

Nic;o)
0

Commented:
I don't think that'll work, Nico.

If the SalesLevel starts out as \$500,100 you'd only give commission on the \$100 at 5%, reduce the SalesLevel down to \$100, so no commission at the 3% or 2% levels (which isn't what is intended)

On my original logic, If the SalesLevel is \$500,100, you'd get 5% on the \$100, reduce the level to \$500,000, get 3% on the \$250,000 (\$500,000-250,000), reduce the level to \$250,00, get 2% on the \$150,000 (250,000-100,000), reduce the level to 100,000 then get 1% on the \$100,000 (100,000-0)

I believe that's what's intended, yes?
0

Author Commented:
Nevaar,

That is what is intended, I will test the final solution in a few.

Dave
0

Author Commented:
One last question,
Why does the result come out to 16,774.9998 instead of 16775 even?

dave
0

Author Commented:
One last question,
Why does the result come out to 16,774.9998 instead of 16775 even?

dave
0

Commented:

Dave,

That's the Access precision. Just use a rounded figure at the correct number of decimals to get the right figure from the function.

Nic;o)
0

Author Commented:
Nico, you're loop didn't work, it never entered the loop if the sales amount was greated than the maximum level amount.

Nevaar, you're loop worked but I would have liked to see a way to optimize using Nico's option of only using the levels required.

Either, its a solution and its acceptable for this particular project because of its smaller size.

Thanks,
Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.