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

[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********@gma il.comwrote in message
news:69******** *************** ***********@p59 g2000hsd.google groups.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
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 18, 11:29*am, "KC-Mass" <connearneyATco mcastDOTnetwrot e:

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" <mspilot...@gma il.comwrote in message

news:69******** *************** ***********@p59 g2000hsd.google groups.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
* 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- 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********@gma il.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" <connearneyATco mcastDOTnetwrot e:

>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" <mspilot...@gma il.comwrote in message

news:69******* *************** ************@p5 9g2000hsd.googl egroups.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
* 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- 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 <tom7744.no.s.. [e-mail]:

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

<mspilot...@gma il.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" <connearneyATco mcastDOTnetwrot e:

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" <mspilot...@gma il.comwrote in message

>news:69******* *************** ************@p5 9g2000hsd.googl egroups.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
* 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- 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.

tblCommissionDe tail
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********@gma il.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 <tom7744.no.s.. [e-mail]:

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

<mspilot...@gm ail.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" <connearneyATco mcastDOTnetwrot e:
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" <mspilot...@gma il.comwrote in message

>>news:69****** *************** *************@p 59g2000hsd.goog legroups.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
* 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- 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.

tblCommissionD etail
tier
rep1Tier
rep2Tier
commission%

Thank you in advance for your advice.
Matt

[spima05]

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

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

<mspilot...@gma il.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 <tom7744.no.s.. [e-mail]:

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

<mspilot...@gma il.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" <connearneyATco mcastDOTnetwrot e:
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" <mspilot...@gma il.comwrote in message

>news:69******* *************** ************@p5 9g2000hsd.googl egroups.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
* 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- 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.

tblCommissionDe tail
* *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 <mspilot...@gma il.comwrote:

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

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

<mspilot...@gma il.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 <tom7744.no.s.. [e-mail]:
>On Sat, 18 Oct 2008 12:19:21 -0700 (PDT), spima05

><mspilot...@gm ail.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" <connearneyATco mcastDOTnetwrot e:
>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" <mspilot...@gma il.comwrote in message

>>news:69****** *************** *************@p 59g2000hsd.goog legroups..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
* 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- 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.

>tblCommissionD etail
* *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********@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

[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.c om...

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