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
flagSaleInitiator

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

[KC-Mass]

Hi Matt

You need to find a way to show your matrix more clearly.
The word wrap makes it unreadable.

I also don't see how/if that matrix references the upline.

Kevin

"spima05" <ms********@gmail.comwrote in message
news:69**********************************@p59g2000 hsd.googlegroups.com...

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
flagSaleInitiator

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 18, 11:29*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:

Hi Matt

You need to find a way to show your matrix more clearly.
The word wrap makes it unreadable.

I also don't see how/if *that matrix references the upline.

Kevin

"spima05" <[e-mail] in message

news:69**********************************@p59g2000 hsd.googlegroups.com...

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
* flagSaleInitiator

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- Hide quoted text -

- Show quoted text -

Hello Kevin

thank you for your reply. I have posted an image of the matrix on the
following website:
http://s90.photobucket.com/albums/k2...onschedule.jpg

and here is a link to see the relationships of the tables
http://s90.photobucket.com/albums/k2...bstructure.jpg

I appreciate any advice you can provide.

Thank you
Matt

[Tom van Stiphout]

On Sat, 18 Oct 2008 12:19:21 -0700 (PDT), spima05
<ms********@gmail.comwrote:

MUCH better. One point about the db design: there should be a PK over
saleOrderID + repID in tblOrderRep.

Why does Allen get 6%? Is that a matter of looking up Allen's tier
(say it is 1) and then staying in the same row in the matrix (so he
gets whatever is in B6)?

-Tom.
Microsoft Access MVP

>On Oct 18, 11:29*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:

>Hi Matt

You need to find a way to show your matrix more clearly.
The word wrap makes it unreadable.

I also don't see how/if *that matrix references the upline.

Kevin

"spima05" <[e-mail] in message

news:69**********************************@p59g200 0hsd.googlegroups.com...

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
* flagSaleInitiator

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- Hide quoted text -

- Show quoted text -

Hello Kevin

thank you for your reply. I have posted an image of the matrix on the
following website:
http://s90.photobucket.com/albums/k2...onschedule.jpg

and here is a link to see the relationships of the tables
http://s90.photobucket.com/albums/k2...bstructure.jpg

I appreciate any advice you can provide.

Thank you
Matt

[spima05]

On Oct 18, 4:18*pm, Tom van Stiphout <[e-mail]:

On Sat, 18 Oct 2008 12:19:21 -0700 (PDT), spima05

<[e-mail]:

MUCH better. One point about the db design: there should be a PK over
saleOrderID + repID in tblOrderRep.

Why does Allen get 6%? *Is that a matter of looking up Allen's tier
(say it is 1) and then staying in the same row in the matrix (so he
gets whatever is in B6)?

-Tom.
Microsoft Access MVP

On Oct 18, 11:29*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:

Hi Matt

You need to find a way to show your matrix more clearly.
The word wrap makes it unreadable.

I also don't see how/if *that matrix references the upline.

Kevin

"spima05" <[e-mail] in message

>news:69**********************************@p59g200 0hsd.googlegroups.com....

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
* flagSaleInitiator

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- Hide quoted text -

- Show quoted text -

Hello Kevin

thank you for your reply. I have posted an image of the matrix on the
following website:
http://s90.photobucket.com/albums/k2...dbimages/?acti...

and here is a link to see the relationships of the tables
http://s90.photobucket.com/albums/k2...dbimages/?acti...

I appreciate any advice you can provide.

Thank you
Matt- Hide quoted text -

- Show quoted text -

Thank you Tom, i have updated the tblOrderRep with primary keys for
the salesOrderID and repID fields.

Yes, Allen receives 6% because he is a Tier 1 rep and he is Roger's
upline.

I am having a difficult time figuring how to store the rates so it is
easy to apply them to the reps involved in the sale. I was thinking of
creating a commissions table as follows, but i dont know if it is a
good idea to have 2 fields for the reps.

tblCommissionDetail
tier
rep1Tier
rep2Tier
commission%

Thank you in advance for your advice.
Matt

[Tom van Stiphout]

On Sat, 18 Oct 2008 14:52:21 -0700 (PDT), spima05
<ms********@gmail.comwrote:

I just want to get the detail about Allen right, because it may matter
for the data structure. What if Allen is also Suzy's uplink and she
and Sally are Tier-4 reps and made a sale together. Then Allen gets 3%
from cell B15, right?

-Tom.

>On Oct 18, 4:18*pm, Tom van Stiphout <[e-mail]:

>On Sat, 18 Oct 2008 12:19:21 -0700 (PDT), spima05

<[e-mail]:

MUCH better. One point about the db design: there should be a PK over
saleOrderID + repID in tblOrderRep.

Why does Allen get 6%? *Is that a matter of looking up Allen's tier
(say it is 1) and then staying in the same row in the matrix (so he
gets whatever is in B6)?

-Tom.
Microsoft Access MVP

>On Oct 18, 11:29*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:
Hi Matt

>You need to find a way to show your matrix more clearly.
The word wrap makes it unreadable.

>I also don't see how/if *that matrix references the upline.

>Kevin

>"spima05" <[e-mail] in message

>>news:69**********************************@p59g20 00hsd.googlegroups.com...

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
* flagSaleInitiator

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- Hide quoted text -

>- Show quoted text -

>Hello Kevin

>thank you for your reply. I have posted an image of the matrix on the
following website:
http://s90.photobucket.com/albums/k2...dbimages/?acti...

>and here is a link to see the relationships of the tables
http://s90.photobucket.com/albums/k2...dbimages/?acti...

>I appreciate any advice you can provide.

>Thank you
Matt- Hide quoted text -

- Show quoted text -

Thank you Tom, i have updated the tblOrderRep with primary keys for
the salesOrderID and repID fields.

Yes, Allen receives 6% because he is a Tier 1 rep and he is Roger's
upline.

I am having a difficult time figuring how to store the rates so it is
easy to apply them to the reps involved in the sale. I was thinking of
creating a commissions table as follows, but i dont know if it is a
good idea to have 2 fields for the reps.

tblCommissionDetail
tier
rep1Tier
rep2Tier
commission%

Thank you in advance for your advice.
Matt

[spima05]

On Oct 18, 6:48*pm, Tom van Stiphout <[e-mail]:

On Sat, 18 Oct 2008 14:52:21 -0700 (PDT), spima05

<[e-mail]:

I just want to get the detail about Allen right, because it may matter
for the data structure. What if Allen is also Suzy's uplink and she
and Sally are Tier-4 reps and made a sale together. Then Allen gets 3%
from cell B15, right?

-Tom.

On Oct 18, 4:18*pm, Tom van Stiphout <[e-mail]:

On Sat, 18 Oct 2008 12:19:21 -0700 (PDT), spima05

<[e-mail]:

MUCH better. One point about the db design: there should be a PK over
saleOrderID + repID in tblOrderRep.

Why does Allen get 6%? *Is that a matter of looking up Allen's tier
(say it is 1) and then staying in the same row in the matrix (so he
gets whatever is in B6)?

-Tom.
Microsoft Access MVP

On Oct 18, 11:29*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:
Hi Matt

You need to find a way to show your matrix more clearly.
The word wrap makes it unreadable.

I also don't see how/if *that matrix references the upline.

Kevin

"spima05" <[e-mail] in message

>news:69**********************************@p59g200 0hsd.googlegroups.com...

Hello

I am developing a database to calculate commissions on a sale foreach
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
* flagSaleInitiator

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- Hide quoted text -

- Show quoted text -

Hello Kevin

thank you for your reply. I have posted an image of the matrix on the
following website:
http://s90.photobucket.com/albums/k2...dbimages/?acti....

and here is a link to see the relationships of the tables
http://s90.photobucket.com/albums/k2...dbimages/?acti....

I appreciate any advice you can provide.

Thank you
Matt- Hide quoted text -

- Show quoted text -

Thank you Tom, i have updated the tblOrderRep with primary keys for
the salesOrderID and repID fields.

Yes, Allen receives 6% because he is a Tier 1 rep and he is Roger's
upline.

I am having a difficult time figuring how to store the rates so it is
easy to apply them to the reps involved in the sale. I was thinking of
creating a commissions table as follows, but i dont know if it is a
good idea to have 2 fields for the reps.

tblCommissionDetail
* *tier
* *rep1Tier
* *rep2Tier
* *commission%

Thank you in advance for your advice.
Matt- Hide quoted text -

- Show quoted text -

Hello Tom

Thank you for your time. As per your example, if Suzy and Sally
completed a sale together and they are both Tier 4 reps and Allen is
the Tier 1 rep in Suzy's upline then Allen would receive 2% shown in
cell B12 because Suzy & Sally are both Tier 4 reps.

I appreciate your help and please feel free to ask any questions.

Thank you
Matt

[spima05]

On Oct 18, 7:33*pm, spima05 <[e-mail]:

On Oct 18, 6:48*pm, Tom van Stiphout <[e-mail]:

On Sat, 18 Oct 2008 14:52:21 -0700 (PDT), spima05

<[e-mail]:

I just want to get the detail about Allen right, because it may matter
for the data structure. What if Allen is also Suzy's uplink and she
and Sally are Tier-4 reps and made a sale together. Then Allen gets 3%
from cell B15, right?

-Tom.

>On Oct 18, 4:18*pm, Tom van Stiphout <[e-mail]:
>On Sat, 18 Oct 2008 12:19:21 -0700 (PDT), spima05

><[e-mail]:

>MUCH better. One point about the db design: there should be a PK over
>saleOrderID + repID in tblOrderRep.

>Why does Allen get 6%? *Is that a matter of looking up Allen's tier
>(say it is 1) and then staying in the same row in the matrix (so he
>gets whatever is in B6)?

>-Tom.
>Microsoft Access MVP

>On Oct 18, 11:29*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:
>Hi Matt

>You need to find a way to show your matrix more clearly.
>The word wrap makes it unreadable.

>I also don't see how/if *that matrix references the upline.

>Kevin

>"spima05" <[e-mail] in message

>>news:69**********************************@p59g20 00hsd.googlegroups..com...

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. Iam
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 onthe
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 repsand
Allen will receive 6% commission on the sale.

DB Structure:
tblOrder
* salesOrderID
* salesAmount
* customerName

tblOrderRep
* salesOrderID
* repID
* flagSaleInitiator

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- Hide quoted text -

>- Show quoted text -

>Hello Kevin

>thank you for your reply. I have posted an image of the matrix on the
>following website:
>http://s90.photobucket.com/albums/k2...dbimages/?acti...

>and here is a link to see the relationships of the tables
>http://s90.photobucket.com/albums/k2...dbimages/?acti...

>I appreciate any advice you can provide.

>Thank you
>Matt- Hide quoted text -

>- Show quoted text -

>Thank you Tom, i have updated the tblOrderRep with primary keys for
>the salesOrderID and repID fields.

>Yes, Allen receives 6% because he is a Tier 1 rep and he is Roger's
>upline.

>I am having a difficult time figuring how to store the rates so it is
>easy to apply them to the reps involved in the sale. I was thinking of
>creating a commissions table as follows, but i dont know if it is a
>good idea to have 2 fields for the reps.

>tblCommissionDetail
* *tier
* *rep1Tier
* *rep2Tier
* *commission%

>Thank you in advance for your advice.
>Matt- Hide quoted text -

- Show quoted text -

Hello Tom

Thank you for your time. As per your example, if Suzy and Sally
completed a sale together and they are both Tier 4 reps and Allen is
the Tier 1 rep in Suzy's upline then Allen would receive 2% shown in
cell B12 because Suzy & Sally are both Tier 4 reps.

I appreciate your help and please feel free to ask any questions.

Thank you
Matt- Hide quoted text -

- Show quoted text -

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

[Tom van Stiphout]

On Sat, 18 Oct 2008 16:42:54 -0700 (PDT), spima05
<ms********@gmail.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

[KC-Mass]

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" <to*************@cox.netwrote in message
news:bp********************************@4ax.com...

On Sat, 18 Oct 2008 16:42:54 -0700 (PDT), spima05
<ms********@gmail.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

[spima05]

On Oct 19, 11:35*am, "KC-Mass" <connearneyATcomcastDOTnetwrote:

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" <[e-mail] in messagenews:bp********************************@4ax .com...

On Sat, 18 Oct 2008 16:42:54 -0700 (PDT), spima05
<[e-mail]:

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********@gmail.comwrote in message
news:98**********************************@i76g2000 hsf.googlegroups.com...
On Oct 19, 11:35 am, "KC-Mass" <connearneyATcomcastDOTnetwrote:

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" <[e-mail] in
messagenews:bp********************************@4ax .com...

On Sat, 18 Oct 2008 16:42:54 -0700 (PDT), spima05
<[e-mail]:

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"
<connearneyATcomcastDOTnetwrote:

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>