:eek:

My boss threw a curve at me this morning and I need some guidence. I created a commission tracking db for our industry (automotive). Each technician will have a base commission that they'll get regardless of their production. Should they exceed their production by X, we would like to give them a bonus of 2% of all of their work. Should they exceed their production by X+Y, we'd like to increased the bonus to a total of 4% for their work during this pay period.

I need help with the table stucture, to start. My db currently contains a variety of tables including tblTech. tblTech contains information specific to each tech including their annual PERSONAL income goal (not to be confused with Bonus scale). Depending on a number of factors (ie. how long they've worked here, how much other responsibility, etc.), I'd like to assign a bonus tier to each technicial. ie. Technicial A = Joe. Joe is promoted to manager and therefore needs a greater bonus for the same amount of production. I'd like to assign him to Tier 2 where he makes more then a Tier 1 one employee.

I'm having a hard time getting focused. All suggestions are appreciated.

Is the bonus tied to production, or your tier, or both?
Is the percentage constant for the tier they are in or unique to each person?
Is the bonus set per production overrun (always 2% of x and 4% of Y per your example)?

First identify all the data that is truly unique to a tech, that needs to go on the tech's record. After that we need to see what your answers are.

Keep in mind that you're going to end up with a calculation nightmare unless you have some way of "closing" a period. Since the commission is a percentage of the production for a period, you can't allow the period to be adjusted or that would change the commission which might already have been paid.

Are returns a possibility? How to they affect the commission? What time period would this have to be left open?

Commission can be extremely complicated to calculate. Make sure you understand ALL the rules. Also, since we're talking about money, auditablity is of course a requirement.

Is the bonus tied to production, or your tier, or both?
Is the percentage constant for the tier they are in or unique to each person?
Is the bonus set per production overrun (always 2% of x and 4% of Y per your example)?

First identify all the data that is truly unique to a tech, that needs to go on the tech's record. After that we need to see what your answers are.

Bonus is tied to production and tier. "Tier" is there in order to put managers on a higher pay scale. Each tier will have their own goals and percentage increase to their base comission.

Is the bonus set per production overrun (always 2% of x and 4% of Y per your example)? Yes. I will need to reference their tier which will determine their goal and % increase to their base. Make sense?

Since the commission is a percentage of the production for a period, you can't allow the period to be adjusted or that would change the commission which might already have been paid.

I think this will be okay if we run off percentages. Right?

Also, there are no refunds. If a customer is upset with the quality of the work, we will re-do the job as many times as we need to get it right.

Anybody have any advice? :D

Well sounds like you need a TIER table that keeps track of the variables. It will need a PK with a FK in each tech's record to tie them to a tier. Now the question becomes, are the amounts set? In other words are there always a first level of bonus and always a second level and never anymore? Do you setup columns to hold that or separate rows? I am always a Serparate rows kind of guy, mainly because you are not tied to limits as much. If next year they want to expand the X is 2% over, Y is 4% over and now Z is 6% over, you have to add columns, where if you go with the rows, you only have to add data (no table changes).
Then as Pat said, how are you going to track what was paid in bonus? They are going to want to see what happened. An actual paid (as an example) table might be the ticket for you on that so you can record (via keys) which tech, at which tier, etc got what on this date.
Just to start you off thinking about it anyway.

Thanks for everyone's help. Working through the idea, we found an alternative means to offer incentive that will be much easier for us to work with.

Thanks again guys/gals.