query to add 1 then change information

[Guest]

If I have a table of employees set up that has included increment amounts
along with pay steps and due dates. Is there a way to run a query on these
fields and when it finds the due date it will add 1 to the increment step
and pull out the amount associated with the new increment?
example: due date: 5/1/2006 - increment step 7 - increment amount $19,000

My other table would be steps and increments.example of a few:
increment steps increment amounts
7 $19,000
8 $24,000
9 $29,000
when I run the query on emp with the due date it will match the step 7 add 1
and then take it to the next step of 8 and bring in the new amount associated
with the new step ($24,000). Can this be done?

 

[Michel Walsh]

Hi,

If ANOTHER table already has the information step+amount, then, in your
WORKING table, store only the step value (easy to increment by one) AND use
a query to join your two tables through their common field, step. The join
will dynamically follow the modification you brought to the working table
step value and will pump, automatically, the right amount, for you, without
having to even think about it! Isn't it nice? that is why it is called a
relational database




Vanderghast, Access MVP