Design Question - volatile info and history

[crimedog]

I have Rep Table & Deal Table


The rep has name, Hire Date, Position, Salary and Quota
The deal Table has Deals sold and the Revenue

The problem : Rep A gets a promotion, a raise, and a new Quota

I have a Query that filters deals by year and month and shows where reps are versus their quota and their profitability (Salary versus quota)
Which is fine if I don't care about Historical data - but if a rep has Quota1 during 2013 and Quota2 during 2014 - When I run Query for 2013 I don't want to compare against new quota...

Here is my thought:
Create a Quota Table plus a Salary Table that relates to Rep Table

Then the Query pulls: Deal Revenue Filtered by month from deal table - Then Quota Filtered by year and month and rep from Quota Table.
Problem: I don't want to add a new Quota every Month - it doesn't happen that often...
How would I filter to get the Quota from the most recent change?

Does this seem reasonable - or is there a better way

[rpeare]

Make a separate table storing the person's position, salary and quota with a start date/end date

So if a person gets a promotion on 7/1/2013 they go from a salary of 40,000 to 50,000 and a quota of 10 to 15 you'd have two records

RepPos_ID  Rep_ID  Pos_ID  Salary  Quota
1          1       1       40000   10
2          1       2       50000   15

You'd just have to update the salary/quota as long as the person didn't change positions if it's possible for those to change within the same position.

The only way to accurately calculate your information is to have a history of a person's promotions which you currently do not have.

[crimedog]

How do you write the query filter for : find the record with the date that is closest to, but not greater than DateX?

[rpeare]

if you're looking for the most recent record of someone's quota/position and you are entering data sequentially you can just look for the largest (max) RepPos_ID (in my example) for a given Rep_ID

SELECT REP_ID, Max(RepPos_ID) as MR_ID FROM TableName

where table name is the name of the one holding quota information