• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2856
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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