Need to increment a Value in a table. (1 Viewer)

[raydan]

I have a database that has a few tables and in one of them (Originators), there is all the info about my loan originators including there comission level. I would like it when they turn in a certain amount of loans there commision level will increase. Is this posible? Here is my DB setup.

Tables:

Originators- 1 to many-
OriginatorID,FirstName,LastName,OrigSSN, Address,City,State,PostalCode,HomePhone,WorkPhone,CellPhone,FaxNumber,EmailName,BirthDate,Level(This is the commision level, and its in %).

Customers- 1 to many-CustomerID,FirstName,LastName,SSN,HomePhone,MobiPhone,FaxNumber,Address,City,State,Zip,Email,BirthDate,CreditScore,SpouseFirst,SpouseLast,SpouceSSN,SpouceBirthDate,SpouceCreditScore,OriginatorID.

Loans- many to 1-
LoanID,LoanType,StartDate,EndDate,LoanLender,CustomerID.

Lenders- 1 to 1-
ID,LoanLender.


When a customer is added to the DB it is done with a form that has OriginatorID(which is autocreated), CustomerID(also auto) and some other of the customer stuff like phone numbers. When this happens I would like to have the originators % level increase, say after every 4 customers entered. Is it possible?

Thanks,

Scott

 

[llkhoutx]

Mere increment won't work because of the potential for deleted records. Just count the appropriate records.

 

[Pat Hartman]

What you are asking how to do does not make sense from a business point of view. You need to get a more detailled understanding of the business rules. Increasing the commission rate on an ever escalating scale after each 4 new customers sounds like a really good deal to me. Where do I sign up. I'll bring in 100 new customers and pretty soon, you'll be paying me more in commission than you are making on the transaction.

Generally, rates that increase based on counts or dollar value are stored in a table and they are applied for a given time frame or as long as the sales person maintains that level. For example, increased commission may be paid after 4 new accounts are added in a single month but the following month the commission on the first 4 new accounts goes back to the base number. So as each new account is added, the salesperson's sales for the period are analyzed and the rate for THIS account is calculated from the rate tables and stored with THIS account to make for easy auditing and detailled reporting to both the salesperson and payroll.

 

[raydan]

Your correct it is a great deal. Just let me know and I can get you set up anytime. Financialy this Corporation is very strong working from this model. Maybe your in the wrong business because the branch manager is making 45k a month. Getting people to sign up is not as easy as you think, when your dealing mortages, Insurance, and securities. I was just trying to see if there is a way to make tracking our loan originators progress easier. I will continue just watching how many new customers and loans they produce and then change the commision levels manually. Also, sorry about posting this in another forum catagory, I just wasn't sure whick topic to post it in. This site is great and very helpful.

Scott