Calculations based on looping tables
Hey. I have an interesting problem that I need help with. I'm hoping there is a clean SQL solution but I suspect I will have to write some code (I'm using MS Access).
I have a table of sales people's commissions. I need to know how much commission each salesperson makes. The problem is that sometimes the commission is split between multiple people.
Example table data for todays commissions:
SP1, $100
SP2, $200
SP3, $300
SP4, $400
Now for the tricky part. If SP3 makes a sale then it gets split between SP1 and SP2 using the following static table data.
Master,Base,SplitPercent
SP1,SP1,100%
SP2,SP2,100%
SP3,SP1,50%
SP3,SP2,50%
SP4,SP3,75%
SP4,SP1,25%
So if you see with SP4, it can get split multiple times, so I can't just do a simple join and then take the percent for each sales person. I somehow have to keep looping through each "Base" until the SplitPercent is 100.
I need to keep track of when the commission gets sent to the Master or the Base as a total sum.
Any ideas? Thanks.