Here's a challenge,
I have a table of sales reps, (name and ID)
I have a table of sales reps graduated commission levels (ID, Level, Rate) -
Jane Smith, $0, 1%
Jane Smith, $100,000, 2%;
Jane Smith, $250,000, 3%;
Jane Smith, $500,000, 5%
I have a table of sales transactions.
The query I'm trying to create is to calculate the sales rep commission for the month. If Jane Smith sells $350,000 for the month, the first $100,000 @ 1% is $1,000 plus the next $150,000 is @ 2% for $3,000 plus the last $100,000 is @ 3% for $3,000 for total commisions of $7,000 for the month
I am pretty sure I can solve this with VBA, but I'd rather a query solution if anyone out there has any ideas.