Sales commission table

[shaunacol]

I am trying to set up a database to work out sales commission but am not sure of the best way to set the tables and relationships. The commission structure for each person may be different. It is based on a percentage of the Gross Profit for that salesperson for invoices paid between 21 and 20th of a certaion month.

However, it maybe that one salesperson is paid 0% on total Profit below 8k but another salesperson may be paid 10% on 0-4k and 20% above 8k. To add to the complexity, a salespersons commission structure can change at any time. I think I need to make the commission for each salesperson into a derived field but dont know how to set up my tables to make that possible because of the potentially complex math. Please help!

 

[MajP]

tblRateRule
salesPersonID
rateStartDate
rateEndDate
minProfit
maxProfit
rate

So for one salesman you could have something like this.

Where rateStartDate and rateEndDate are the period the rate rule was in effect. You can either build a custom function or do this in a query. If it is a custom function

public function getRate(ID, effectiveDate, Profit) as double
use a dlookup to return the rate where
salespersonID = ID
and effectiveDate >= rateBeginDate and effectiveDate<= rateEndDate
and Profit >= minProfit and <= maxProfit
end function

You can do the same in a query to get the rate

 

[dhookom]

I agree with MajP on this solution. The only thing I might change would be if several sales persons might have the same "rate plan". You could remove the salesPersonID and add a RatePlanID. Then you would create an Sales Person/Rate Plan history table.

Duane
Hook'D on Access
MS Access MVP