Solved

Graduated Commission Scale

Posted on 2002-07-09
21
2,756 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
  • 11
  • 6
  • 3
  • +1
21 Comments
 
LVL 11

Expert Comment

by:LambertHeenan
Comment Utility
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
Comment Utility
Except each salesperson would have different levels and rates at each level
0
 
LVL 54

Expert Comment

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

by:dcaparaso
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dcaparaso
Comment Utility
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
Comment Utility
Correct !

OrderBy ID, Level

will do !

Nic;o)

0
 
LVL 4

Accepted Solution

by:
Nevaar earned 200 total points
Comment Utility
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
Comment Utility
Oops, the if line should be:

if SalesLevel > rst_commission_structure![level] then

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
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
Comment Utility
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
Comment Utility
Nevaar,

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

Dave
0
 

Author Comment

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

dave
0
 

Author Comment

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now