Solved

# Graduated Commission Scale

Posted on 2002-07-09
2,756 Views
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
Question by:dcaparaso
• 11
• 6
• 3
• +1

LVL 11

Expert Comment

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

Except each salesperson would have different levels and rates at each level
0

LVL 54

Expert Comment

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

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

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

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

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

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

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

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

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

Correct !

OrderBy ID, Level

will do !

Nic;o)

0

LVL 4

Accepted Solution

Nevaar earned 200 total points
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

Oops, the if line should be:

if SalesLevel > rst_commission_structure![level] then

0

LVL 54

Expert Comment

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

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

Nevaar,

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

Dave
0

Author Comment

One last question,
Why does the result come out to 16,774.9998 instead of 16775 even?

dave
0

Author Comment

One last question,
Why does the result come out to 16,774.9998 instead of 16775 even?

dave
0

LVL 54

Expert Comment

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

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

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.

#### Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!