Solved

Graduated Commission Scale

Posted on 2002-07-09
21
2,806 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 200 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 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