Tiered Commission Calculation

[spima05]

Hello

I am developing a database to calculate commissions on a sale for each
rep involved in the same and their uplines.

Below is the database structure and the commissions schedule. I am
having trouble designing a way to store the commission rates and
calculate the commission amounts. Below is a list of the business
rules for calculating the commisssions.

1) each sale will have only 1 or 2 reps involved with the sale
2) each sale will have only 1 sale orginator (flag in tblOrderRep)
3) each rep involved with the sale will receive a commission on the
sale based on the commission matrix below
4) the uplines of the rep that is flagged as the sale originator will
receive a commission on the sale based on the matrix below

For example:
- Roger & Matt completed a sale together.
- Roger & Matt are both Tier 2 reps.
- Roger was the sale originator
- Allen is Roger's upline
- Roger & Matt will split the 8% commission for the tier 2 reps and
Allen will receive 6% commission on the sale.

DB Structure:
tblOrder
salesOrderID
salesAmount
customerName

tblOrderRep
salesOrderID
repID
flagSaleInitiat or

tblRep
repID
repName
tier
upline

Commission Matrix
Tier 1 Tier 2 Tier
3 Tier 4
Reps that Made the sale Commission Commission
Commission Commission
Sales Rep in Tier 1 10%
2 Sales Reps in Tier 1 5% each
Sales Reps in Tier 1 & 2 10% 4%
Sales Rep in Tier 2 6% 8%
2 Sales Reps in Tier 2 6% 4% each
Sales Reps in Tier 2 & 3 4%
8% 4%
Sales Rep in Tier 3 3%
4% 8%
2 Sales Reps in Tier 3 3%
4% 4% each
Sales Reps in Tier 1 & 3 10%
2% 4%
Sales Rep in Tier 4 2%
3% 4% 8%
2 Sales Reps in Tier 4 2%
3% 4% 4% each
Sales Reps in Tier 1 & 4 10%
2% 1% 4%
Sales Reps in Tier 2 & 4 3%
4% 2% 4%
Sales Reps in Tier 3 & 4 3%
2% 3% 4%

Thank you in advance for your time and advice
Matt

[spima05]

On Oct 19, 11:35*am, "KC-Mass" <connearneyATco mcastDOTnetwrot e:

Hi Matt

Just two questions if I could:

1. *In your matrix where there are two reps, the one in the lower
(numerically) tier
(1 vs 3) seems always to get a full commission and the one in the higher
tier
(3 in the above example) always gets a half commission. *Does that assume
that the one in the lower tier is the originator or do commission rates
depend
soley on relative tier level of the participating reps?

2. On row 8 of your matrix, the single rep will receive 8%. *What do the
3% and 4% in columns B and C represent - who are they applied to?
If its the upline what happens in row 10?

I do think if you get the structure right you can establish commissions with
a query.

Kevin

"Tom van Stiphout" <tom7744.no.s.. [e-mail] in messagenews:bp* *************** *************** *@4ax.com...

On Sat, 18 Oct 2008 16:42:54 -0700 (PDT), spima05
<mspilot...@gma il.comwrote:

Ha, you left that gem for last, right :-)

I think the data structure doesn't matter as much as I first thought:
you'll have to write some VBA with recordsets to figure this out.
Access doesn't do recursive queries (unlike SQL Server).
You probably know you can call a VBA function from a query.
If performance is a concern, I might use a Table type recordset, and
use Seek to find the record I'm interested in.
It will probably take a few hours to program, but once the business
rules are firmly established the rest follows rather mechanically.

Best of luck,

-Tom.

<clip>

>Hello Tom

>I forgot to mention, in the example we were going thru above, the Tier
2 & Tier 3 reps that are in Suzy's upline will also receive a
commission of 3% (cell C12) & 4% (cell D12) that will need to be
calculated.

>thank you
Matt- Hide quoted text -

- Show quoted text -

Hello Kevin

Thank you for your time. Regarding your questions:
Commission rates depend soley on the tier level of the participating
reps. Using your example of a Tier 1 & Tier 3 rep performing a sale
together (row 10 of the commission matrix), the Tier 1 rep receives
10%, the Tier 3 rep receives 4% and the Tier 2 rep receives 2% because
they recruited the Tier 3 rep. All reps that have downlines receive a
commission any time their downlines make a sale.

Thank you again for any advice you can offer on how to design the
table to store the commission rates and how to apply them using a
query.
Matt

[KC-Mass]

Hi Matt

I think you've worn me out.

I asked two questions, although I will agree that the second was a two part
question. I got one point five answers.
I do not ask questions here to expand my knowledge but to make my response
responsive to your specific needs.
I've heard nothing re row 8 and now you talk about downlines when before we
were only concerned with uplines.
When describing data, consistency and specificity matter - a great deal.

You like me, and many others, assume that everyone will understand "OUR"
terminolgy and vernacular. They don't and won't.

If you won't describe your conditions and needs specifically and in detail -
those willing to help will be unable to do so.
Trust me I have been guilty of all the same faults here and hopefully have
learned from those experiences.

Food for thought.

Regards
Kevin


"spima05" <ms********@gma il.comwrote in message
news:98******** *************** ***********@i76 g2000hsf.google groups.com...
On Oct 19, 11:35 am, "KC-Mass" <connearneyATco mcastDOTnetwrot e:

Hi Matt

Just two questions if I could:

1. In your matrix where there are two reps, the one in the lower
(numerically) tier
(1 vs 3) seems always to get a full commission and the one in the higher
tier
(3 in the above example) always gets a half commission. Does that assume
that the one in the lower tier is the originator or do commission rates
depend
soley on relative tier level of the participating reps?

2. On row 8 of your matrix, the single rep will receive 8%. What do the
3% and 4% in columns B and C represent - who are they applied to?
If its the upline what happens in row 10?

I do think if you get the structure right you can establish commissions
with
a query.

Kevin

"Tom van Stiphout" <tom7744.no.s.. [e-mail] in
messagenews:bp* *************** *************** *@4ax.com...

On Sat, 18 Oct 2008 16:42:54 -0700 (PDT), spima05
<mspilot...@gma il.comwrote:

Ha, you left that gem for last, right :-)

I think the data structure doesn't matter as much as I first thought:
you'll have to write some VBA with recordsets to figure this out.
Access doesn't do recursive queries (unlike SQL Server).
You probably know you can call a VBA function from a query.
If performance is a concern, I might use a Table type recordset, and
use Seek to find the record I'm interested in.
It will probably take a few hours to program, but once the business
rules are firmly established the rest follows rather mechanically.

Best of luck,

-Tom.

<clip>

>Hello Tom

>I forgot to mention, in the example we were going thru above, the Tier
2 & Tier 3 reps that are in Suzy's upline will also receive a
commission of 3% (cell C12) & 4% (cell D12) that will need to be
calculated.

>thank you
Matt- Hide quoted text -

- Show quoted text -

Hello Kevin

Thank you for your time. Regarding your questions:
Commission rates depend soley on the tier level of the participating
reps. Using your example of a Tier 1 & Tier 3 rep performing a sale
together (row 10 of the commission matrix), the Tier 1 rep receives
10%, the Tier 3 rep receives 4% and the Tier 2 rep receives 2% because
they recruited the Tier 3 rep. All reps that have downlines receive a
commission any time their downlines make a sale.

Thank you again for any advice you can offer on how to design the
table to store the commission rates and how to apply them using a
query.
Matt

[Tom van Stiphout]

On Mon, 20 Oct 2008 00:13:37 -0400, "KC-Mass"
<connearneyATco mcastDOTnetwrot e:

I second that. For example it wasn't until third or fourth instance
that I realized the recursive (or at least hierarchical) nature of
your commission structure. And I want to add that writing down all
requirements in unambiguous language sometimes reveals the solution.
It forces you to think out of the box.

Now it may well be that after reviewing that list some of us may deem
this beyond providing assistance in a newsgroup and may be thinking
about billable time, but that doesn't make the exercise less useful.

-Tom.
Microsoft Access MVP

>Hi Matt

I think you've worn me out.

I asked two questions, although I will agree that the second was a two part
question. I got one point five answers.
I do not ask questions here to expand my knowledge but to make my response
responsive to your specific needs.
I've heard nothing re row 8 and now you talk about downlines when before we
were only concerned with uplines.
When describing data, consistency and specificity matter - a great deal.

You like me, and many others, assume that everyone will understand "OUR"
terminolgy and vernacular. They don't and won't.

If you won't describe your conditions and needs specifically and in detail -
those willing to help will be unable to do so.
Trust me I have been guilty of all the same faults here and hopefully have
learned from those experiences.

Food for thought.

Regards
Kevin

<clip>