<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
Hello<p> I am developing a database to calculate commissions on a
sale for each rep involved in the sale and their uplines.
</p>
<p>Below is the database structure and the commissions schedule. I am
having
trouble designing a way to calculate the commissions for each rep and
upline.</p>
<p>Below is a list of the business rules for calculating the
commissions.
<br>
1) each sale will have only 1 or 2 reps involved with the sale
<br>
2) each sale will have only 1 sale originator (flag in tblOrderRep)
<br>
3) each rep involved with the sale will receive a commission on the
sale based on the commission matrix below
<br>
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
<br>
<br>
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.
<br>
<br>
<b>DB Structure</b><br>
<a href="http://s90.photobucket.com/albums/k255/
spima05/10-17-08dbimages/?action=view¤t=dbstructure.jpg"
target="_blank"><img src="http://i90.photobucket.com/albums/k255/
spima05/10-17-08dbimages/dbstructure.jpg" border="0" alt="Photobucket"
width="684" height="156"></a>
<br>
<br>
<b>Commission Matrix<br>
</b> <a href="http://s90.photobucket.com/albums/k255/
spima05/10-17-08dbimages/?action=view¤t=commissionschedule.jpg"
target="_blank"><img src="http://i90.photobucket.com/albums/k255/
spima05/10-17-08dbimages/commissionschedule.jpg" border="0"
alt="Photobucket" width="641" height="346"></a>
</p>
<p>Thank you in advance for your time and advice
<br>
Matt <br>
</p>
</body>
</html>