Sales Commissions - Conceptual DB Question

[Joe Williams]

I have to develop a program to track sales commissions. I have a standard
Sales Order Header/Detail table design already setup to track the sales.

The issue I am having is that the sales commission % changes as the salesman
get more and more sales for the month.For instance, it starts @ 10%, then if
the salesman sells over 50 orders, it goes to 20%, and on and on from there.

I am trying to determine the most efficient way to do this, right now I can
only think of a dlookup to add up the salesman's orders for the month every
time they open a new order form, but is there a more efficient method? I can
see this would get cumbersome if there were many users and thousands of
sales per month.

Any other considerations to be aware of with a sliding commissions scale
such as this?

Thanks

joe

 

[Damian S]

Hi Joe,

If you are doing the commissions in real time (ie: while they are inputting
their orders) then you probably don't have any better way of doing it... I
guess you could write a function to return the rate for you, but essentially
you would still be querying the data to check if the various conditions have
been met.

Damian.