Improve company productivity with a Business Account.Sign Up

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

Graduated Commission Scale

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
Asked:
dcaparaso
  • 11
  • 6
  • 3
  • +1
1 Solution
 
LambertHeenanCommented:
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
 
dcaparasoAuthor Commented:
Except each salesperson would have different levels and rates at each level
0
 
nico5038Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
dcaparasoAuthor 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
 
nico5038Commented:
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
 
dcaparasoAuthor 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
 
dcaparasoAuthor 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

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
 
dcaparasoAuthor 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

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
 
nico5038Commented:
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
 
dcaparasoAuthor 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
 
dcaparasoAuthor 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
 
nico5038Commented:
Correct !

OrderBy ID, Level

will do !

Nic;o)

0
 
NevaarCommented:
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
 
NevaarCommented:
Oops, the if line should be:

if SalesLevel > rst_commission_structure![level] then

0
 
nico5038Commented:
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
 
NevaarCommented:
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
 
dcaparasoAuthor Commented:
Nevaar,

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

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

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

dave
0
 
nico5038Commented:
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
 
dcaparasoAuthor 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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 11
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now