"Effective Date" Criteria for Rates

I have been stuck on this for some time now. I will try to make it as clear
as I can.

I need to create a Report of “transactions� (days worked of various
employees) that have been assigned a specific invoice number. These reports
need to include calculated “amounts� (hours worked x rate).

Different employees have different rates and the rates will change over
time, the rates are always assigned an “effective date�. The rate that is
used to calculate the “amount� must use the rate that was effective on the
date that the transaction occurred.

I query all transactions (from the transaction table) to find those with the
specified Invoice Number(the transaction table includes date, employee, hours
worked, etc.). Now I need to assign a rate (from the rate table) to each
transaction based on the date the transaction occurred. Access must look at
the employee, then choose the largest “effective date� which is less than the
transaction date. Lastly, I need to perform the “amount� calc.

Can I set up a single query to do all of this? I know I can do it if each
employee only has a single rate (in the rate table). I just don’t know how
to include the criteria to choose the correct effective date.

Let me know if more information is needed……thanks in advance.