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.