分层佣金计算 [英] Tiered Commission Calculation
问题描述
您好
我正在开发一个数据库，用于计算每个
代表销售的佣金以及相同的上线。
以下是数据库结构和佣金计划。我在设计存储佣金率的方法时遇到了麻烦，而b
计算了佣金金额。以下是用于计算佣金的商业名单
。
1）每次销售只有1或2个销售参与销售/>
2）每次销售只有1个销售orginator（旗帜在tblOrderRep）
3）每个销售代表将收到佣金
基于以下佣金矩阵的销售
4）被标记为销售发起人的代表的上线将根据以下矩阵收取销售佣金
例如：
- Roger&马特一起完成了一笔交易。
- Roger& Matt都是Tier 2代表。
- Roger是销售发起人
- Allen是Roger的上线
- Roger& Matt将分拆2级代表的8％佣金和
Allen将在销售中获得6％的佣金。
DB结构：
tblOrder
salesOrderID
salesAmount
customerName
tblOrderRep
salesOrderID
repID
flagSaleInitiator
tblRep
repID
repName
tier
upline
佣金矩阵
Tier 1 Tier 2 Tier
3级4
代表销售佣金佣金的代表
委员会委员会
第1级销售代表10％
2第1层销售代表每个5％
第1层和第1层的销售代表2 10％4％
第2层销售代表6％8％
2第2层销售代表6％4％每个
销售额第2层和第2层的代表3 4％
8％4％
第3层销售代表3％
4％8％
2级销售代表3％3％
4％4％
第1级及以上销售代表3 10％
2％4％
第4层销售代表2％
3％4％8％
2第4层销售代表2％
3％4％4％
第1层和第1层的销售代表4 10％
2％1％4％
第2层和第2层的销售代表4 3％
4％2％4％
第3层和第3层的销售代表4 3％
2％3％4％
提前感谢您的时间和建议
Matt
您好Matt
您需要找到一种更清晰地显示矩阵的方法。
自动换行使其无法读取。
我也不知道该矩阵如何引用上线。
凯文
" spima05" < ms ******** @ gmail.comwrote in message
news：69 ********************** ************ @ p59g2000 hsd.googlegroups.com ...
你好
我正在开发一个数据库，用于计算每个
代表的销售佣金以及相同的上线。
以下是数据库结构和佣金计划。我在设计存储佣金率的方法时遇到了麻烦，而b
计算了佣金金额。以下是用于计算佣金的商业名单
。
1）每次销售只有1或2个销售参与销售/>
2）每次销售只有1个销售orginator（旗帜在tblOrderRep）
3）每个销售代表将收到佣金
基于以下佣金矩阵的销售
4）被标记为销售发起人的代表的上线将根据以下矩阵收取销售佣金
例如：
- Roger&马特一起完成了一笔交易。
- Roger& Matt都是Tier 2代表。
- Roger是销售发起人
- Allen是Roger的上线
- Roger& Matt将分拆2级代表的8％佣金和
Allen将在销售中获得6％的佣金。
DB结构：
tblOrder
salesOrderID
salesAmount
customerName
tblOrderRep
salesOrderID
repID
flagSaleInitiator
tblRep
repID
repName
tier
upline
佣金矩阵
Tier 1 Tier 2 Tier
3级4
代表销售佣金佣金的代表
委员会委员会
第1级销售代表10％
2第1层销售代表每个5％
第1层和第1层的销售代表2 10％4％
第2层销售代表6％8％
2第2层销售代表6％4％每个
销售额第2层和第2层的代表3 4％
8％4％
第3层销售代表3％
4％8％
2级销售代表3％3％
4％4％
第1级及以上销售代表3 10％
2％4％
第4层销售代表2％
3％4％8％
2第4层销售代表2％
3％4％4％
第1层和第1层的销售代表4 10％
2％1％4％
第2层和第2层的销售代表4 3％
4％2％4％
第3层和第3层的销售代表4 3％
2％3％4％
提前感谢您的时间和建议
Matt
10月18日，11：29 * am，KC-Mass。 < connearneyATcomcastDOTnetwrote：
Hi Matt
您需要找到一种方法来更清楚地显示矩阵。
自动换行使其无法读取。
我也看不到该矩阵如何引用上线。
Kevin
" spima05" < mspilot ... @ gmail.com写信息
新闻：69 *********************** *********** @ p59g2000 hsd.googlegroups.com ...
Hello
我正在开发一个数据库来计算每个
代表的销售佣金以及相同的上线。
以下是数据库结构和佣金计划。我在设计存储佣金率的方法时遇到了麻烦，而b
计算了佣金金额。以下是用于计算佣金的商业
规则列表。
1）每次促销只涉及1或2次销售代表
2）每次促销只有1个销售orginator（旗帜在tblOrderRep）
3）参与此次销售的每位代表将根据以下佣金矩阵收到
销售佣金
4）被标记为销售发起人的代表的上线将根据
例如：
- Roger&马特一起完成了一笔交易。
- Roger& Matt都是Tier 2代表。
- Roger是销售发起人
- Allen是Roger的上线
- Roger& Matt将分拆2级代表的8％佣金和
Allen将在销售中获得6％的佣金。
DB结构：
tblOrder
* salesOrderID
* salesAmount
* customerName
tblOrderRep
* salesOrderID
* repID
* flagSaleInitiator
tblRep
* repID
* repName
* tier
* upline
佣金矩阵
* * * * * * Tier 1 * * * * * * * * Tier 2 * * * * * * * Tier
3 * * * * * * * * Tier 4
代表销售* * * *佣金* *佣金
佣金* * *佣金
销售代表1级* * * * * * * * 10％
2第1层的销售代表* * * * * * *每个5％
第1层和第1层的销售代表2 * * * * 10％* * * * * * * * 4％
第2层销售代表* * * * * * * * * 6％* * * * * * * * 8 ％
2第2层的销售代表* * * * * * * 6％* * * * * * * *每个4％
第2层和第2层的销售代表3 * * * * * 4％
8％* * * * * * * * 4％
第3层销售代表* * * * * * * * * 3％
4％* * * * * * * * 8％
2第3层销售代表* * * * * * 3％
4％* * * * * * * *每个4％
第1层和第1层的销售代表3 * * * * 10％
2％* * * * * * * * 4％
销售代表在第4层* * * * * * * * 2％
3％* * * * * * * * 4％* * * * * * * * * * * * 8％
2第4层销售代表* * * * * * 2％
3％* * * * * * * * 4％* * * * * * * * * * *每个4％
销售代表在第1层和第1层4 * * * * 10％
2％* * * * * * * * 1％* * * * * * * * * * * 4％
销售代表在第2层和第2层4 * * * * * 3％
4％* * * * * * * * 2％* * * * * * * * * * * 4％
销售额第3级和第3级的代表4 * * * * * 3％
2％* * * * * * * * 3％* * * * * * * * * * * 4％
< blockquote class =post_quotes>
提前感谢您的时间和建议
Matt-隐藏引用的文字 -
- 显示引用的文字 -
你好凯文
谢谢你的回复。我已在以下网站的
上发布了矩阵图像：
http://s90.photobucket.com/albums/k2...onschedule.jpg
这里是一个链接，可以看到表格的关系
http://s90.photobucket.com/albums/k2...bstructure.jpg
我感谢您提供的任何建议。
谢谢
Matt
2008年10月18日星期六12:19:21 -0700（PDT），spima05
< ms ******** @ gmail.comwrote：
好多了。有关db设计的一点：在tblOrderRep中应该有一个PK超过
saleOrderID + repID。
为什么Allen得到6％？这是一个查找Allen的等级
（比如它是1）然后保持在矩阵中的同一行（所以他
得到的是什么在B6）？
-Tom。
Microsoft Access MVP
> 10月18日，11：29 * am，KC-Mass。 < connearneyATcomcastDOTnetwrote：
> Hi Matt
你需要找到一种方法来更清楚地显示你的矩阵。
自动换行使它变得难以理解。
我也不知道该矩阵如何引用上线。
凯文
" spima05" < mspilot ... @ gmail.com写信息
新闻：69 *************************** ******* @ p59g200 0hsd.googlegroups.com ...
Hello
我正在开发一个数据库来计算每个
代表销售的佣金以及相同的上线。
以下是数据库结构和佣金计划。我在设计存储佣金率的方法时遇到了麻烦，而b
计算了佣金金额。以下是用于计算佣金的商业
规则列表。
1）每次促销只涉及1或2次销售代表
2）每次促销只有1个销售orginator（旗帜在tblOrderRep）
3）参与此次销售的每位代表将根据以下佣金矩阵收到
销售佣金
4）被标记为销售发起人的代表的上线将根据
例如：
- Roger&马特一起完成了一笔交易。
- Roger& Matt都是Tier 2代表。
- Roger是销售发起人
- Allen是Roger的上线
- Roger& Matt将分拆2级代表的8％佣金和
Allen将在销售中获得6％的佣金。
DB结构：
tblOrder
* salesOrderID
* salesAmount
* customerName
tblOrderRep
* salesOrderID
* repID
* flagSaleInitiator
tblRep
* repID
* repName
* tier
* upline
佣金矩阵
* * * * * * Tier 1 * * * * * * * * Tier 2 * * * * * * * Tier
3 * * * * * * * * Tier 4
代表销售* * * *佣金* *佣金
佣金* * *佣金
销售代表1级* * * * * * * * 10％
2第1层的销售代表* * * * * * *每个5％
第1层和第1层的销售代表2 * * * * 10％* * * * * * * * 4％
第2层销售代表* * * * * * * * * 6％* * * * * * * * 8 ％
2第2层的销售代表* * * * * * * 6％* * * * * * * *每个4％
第2层和第2层的销售代表3 * * * * * 4％
8％* * * * * * * * 4％
第3层销售代表* * * * * * * * * 3％
4％* * * * * * * * 8％
2第3层销售代表* * * * * * 3％
4％* * * * * * * *每个4％
第1层和第1层的销售代表3 * * * * 10％
2％* * * * * * * * 4％
销售代表在第4层* * * * * * * * 2％
3％* * * * * * * * 4％* * * * * * * * * * * * 8％
2第4层销售代表* * * * * * 2％
3％* * * * * * * * 4％* * * * * * * * * * *每个4％
销售代表在第1层和第1层4 * * * * 10％
2％* * * * * * * * 1％* * * * * * * * * * * 4％
销售代表在第2层和第2层4 * * * * * 3％
4％* * * * * * * * 2％* * * * * * * * * * * 4％
销售额第3级和第3级的代表4 * * * * * 3％
2％* * * * * * * * 3％* * * * * * * * * * * 4％
< blockquote class =post_quotes>
提前感谢您的时间和建议
Matt-隐藏引用的文字 -
- 显示引用的文字 -
你好凯文
谢谢你的回复。我在以下网站上发布了矩阵的图像：
http://s90.photobucket.com/albums/k2...onschedule.jpg
这里有一个链接来查看表格的关系
http://s90.photobucket.com/albums/k2...bstructure.jpg
我很感激您可以提供的建议。
谢谢
Matt
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
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
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" <mspilot...@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- 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
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" <mspilot...@gmail.comwrote 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
这篇关于分层佣金计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
