?
Solved

Graduated Commission Scale

Posted on 2002-07-09
21
Medium Priority
?
2,821 Views
Last Modified: 2012-06-27
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
Comment
Question by:dcaparaso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 3
  • +1
21 Comments
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 7141231
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 Comment

by:dcaparaso
ID: 7141257
Except each salesperson would have different levels and rates at each level
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7141273
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:dcaparaso
ID: 7141293
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7141325
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 Comment

by:dcaparaso
ID: 7141329
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 Comment

by:dcaparaso
ID: 7141373
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

Dim rst_sales_amt As ADODB.Recordset
Dim rst_commission_structure As ADODB.Recordset

Dim curcon As ADODB.Connection

Set rst_sales_amt = New ADODB.Recordset
Set rst_commission_structure = New ADODB.Recordset

With rst_sales_amt
    .Open "qry_sales_total", curcon, adOpenKeyset, adLockOptimistic
    .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 Comment

by:dcaparaso
ID: 7141377
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

Dim rst_sales_amt As ADODB.Recordset
Dim rst_commission_structure As ADODB.Recordset

Dim curcon As ADODB.Connection

Set rst_sales_amt = New ADODB.Recordset
Set rst_commission_structure = New ADODB.Recordset

With rst_sales_amt
    .Open "qry_sales_total", curcon, adOpenKeyset, adLockOptimistic
    .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
 
LVL 54

Expert Comment

by:nico5038
ID: 7141404
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.
So I would initially start with a ".movefirst"
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?

BTW is ADO needed?

Nic;o)
0
 

Author Comment

by:dcaparaso
ID: 7141466
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 Comment

by:dcaparaso
ID: 7141509
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7141519
Correct !

OrderBy ID, Level

will do !

Nic;o)

0
 
LVL 4

Accepted Solution

by:
Nevaar earned 600 total points
ID: 7141551
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
 
LVL 4

Expert Comment

by:Nevaar
ID: 7141555
Oops, the if line should be:

if SalesLevel > rst_commission_structure![level] then

0
 
LVL 54

Expert Comment

by:nico5038
ID: 7141610
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
 
LVL 4

Expert Comment

by:Nevaar
ID: 7141663
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 Comment

by:dcaparaso
ID: 7141705
Nevaar,

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

Dave
0
 

Author Comment

by:dcaparaso
ID: 7141759
One last question,
Why does the result come out to 16,774.9998 instead of 16775 even?

dave
0
 

Author Comment

by:dcaparaso
ID: 7141802
One last question,
Why does the result come out to 16,774.9998 instead of 16775 even?

dave
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7141865
Yes Nevaar, sample of bad reading....

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 Comment

by:dcaparaso
ID: 7141882
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question