Hi

I think the easiest way to demonstate this is will some sample input and output:

tblPeriods
Code:
Period
1
2
3
4
5
tblRates
Code:
ID|PeriodStart|PeriodEnd|Rate
A|1|2|10
A|3||5
B|1|3|7
B|4|4|9
B|5||11
Desired output is something like

qryOutput
Code:
Period|ID|Rate
1|A|10
2|A|10
3|A|5
4|A|5
5|A|5
1|B|7
2|B|7
3|B|7
4|B|9
5|B|11
So tblRates shows that starting in Period 1, up to and including Period 2, the correct rate was 10. From Period 3 onwards Rate = 5



The application is for a payroll system: An Employee's rate is X from Jan-10 to Jun-10, whereupon they get a promotion. So Rate from Jul-10 onwards is Y. Of course you could re-input the rate for every month between Jan-10 and Jun-10, but that seems inelegant, and with a lot of employees, impractical.

Any help with a solution would be greatly appreciated.