Variable commission percentages

[typhoonikan]

Hello again!

I have a question concerning commission.

I have a database which holds production for many employees. Employees have changing commission rates. It may begin at 50% commission, then could go up to 100%. The user will change the commission rates after a period of time, depending on circumstances.

Each production of course has an employee.

My question is,
Is there a way to lock the commission % in, so that the older productions with lesser or different commissions won't change?

For example:
Production A, I had 50% commission on so I earned say, $100.
By the time Production R came around though 8 months later, my commission had been upped to 80%.
I do not want production A's data to be changed upon upgrading commission.

Does this make sense?

 

[pbaldy]

It sounds like the commission rate should be saved with the production record, just as you might save the sales price of a product with the sales record.

 

[typhoonikan]

But, isn't it possible for when a particular employee is selected, the commission % populates to the current commission value for that employee?

 

[pbaldy]

Absolutely, just as you would populate price when selecting a product. One method (the second):

http://www.baldyweb.com/Autofill.htm

 

[jdraw]

I don't know exactly what tables you have but the concept is that the commission for doing something at some point in time should be considered as the AgreedToCommission. That is you store this AgreedToCommission with the other data (typically OrderLineItem type level). You can have a CurrentCommission stored with the Person, or the product whatever makes sense in your environment. But that is not where you determine the Commission for building the Item. It may be the base of soome calculation, but you should store the AgreedToCommission with the "transaction" (line Item or whatever). You can always change the CurrentCommission and not affect the AgreedToCommission values stored in existing/historical records.

see post #3 at
http://www.accessforums.net/databas...tory-records-current-price-records-29807.html

for a similar set up with Price.

 

[CJ_London]

Another way is to have a commisson table

EmpID   CRate    RFrom
1         50%    1/1/12
1         60%    1/8/12
1         75%    1/9/12
1         100%   1/1/13

Then utilise a sub query to select the rate - note there is no join between the tables

SELECT * FROM PRODUCTION, TblCommission WHERE tblCommission.RFrom = (SELECT max(Rfrom) from TblCommission as Tmp WHERE Rfrom <=PRODUCTION.ProductionDate AND EmpID=PRODUCTION.EmployeeID)

You'll need to substitute your own table and field names

 

[Pat Hartman]

CJ - Is that query updateable? Or does the Max() make it not-updateable?

I sometimes use the history concept but only when the client actually wants to track history without using transaction data to recreate it. Otherwise, it is easier to save the current commission/sales price/whatever with the transaction record. This method has the additional benefit of allowing overrides as the transaction is being created. For example, the current commission is 25% but for a particular job, the company agrees to pay 27%. This doesn't change the rate permanently. It only changes it once.

 

[CJ_London]

Pat

Don't disagree with what you are saying - particularly relevant for financial records (e.g. invoice) - I was just suggesting other options which I think will be relevant to typhoonikan - I have considerable experience in commission systems and with this type of system, you need a historic view for ownership of events because calculations are typically year to date less what has been paid to date and you need to be able to make corrections automatically when people change roles, accounts change management etc and are often advised even months after the event - saves a lot of manual recalculation to put through an adjustment.

With regards updateable sometimes it doesn't work in an update query - you'll have to check. I have a SQL template below which I use in my data importer which uses a max subquery within {SrcTableQry} which limits the number of records returned to what would be returned on the inner join. It's only there to improve performance - without it, the query can take twice as long to run (or even hours).

UPDATE {DstTable} INNER JOIN {SrcTableQry} AS TMP ON {DstTable}.SID=TMP.SID SET {UpdateFields} WHERE {Changed}

Sorry - a bit long winded!

 

[gemma-the-husky]

this sort of thing is tailor made for storing redundant data

although you may have a table storing commission rates and histories, joining records to such a table generally needs a non-equijoin (ie matching a date to a next-lower date).

alternatively you could store the ID of the active commission record, even though that could be determined from the date.

I think this is one occasion when it is worth storing redundant data.