I have used the topic you referenced to develop the following SQL script.

SELECT
tblLabourTransaction.ReferenceNumber, tblLabourTransaction.ActivityDate,
tblLabourTransaction.Hours, tblCraftCodes.EffectiveDate, tblCraftCodes.Rate,
[Hours]*[Rate] AS Amount
FROM
(tblLabourTransaction
INNER JOIN
[SELECT
tblLabourTransaction.ActivityDate
, Max(tblCraftCodes.EffectiveDate) as MxEffDt
FROM
tblCraftCodes
INNER JOIN
tblLabourTransaction
ON
tblCraftCodes.EffectiveDate = tblLabourTransaction.ActivityDate
GROUP BY
tblLabourTransaction.ActivityDate]. AS CR
ON tblLabourTransaction.ActivityDate = CR.ActivityDate)
INNER JOIN
tblCraftCodes
ON
CR.MxEffDt = tblCraftCodes.EffectiveDate;

Below is the result of running this query. As you can see, there are
multile records for some of the ReferenceNumbers.

Reference Activity
Number Date Hours EffectiveDate Rate Amount
000208 01-Jun-05 10 01-Sep-04 $82.00 820
000208 01-Jun-05 10 01-Sep-04 $83.00 830
000208 01-Jun-05 10 01-Sep-04 $83.00 830
000208 01-Jun-05 10 01-Sep-04 $82.00 820
000208 01-Jun-05 10 01-Sep-04 $221.00 2210
000208 01-Jun-05 10 01-Sep-04 $221.00 2210
000209 08-Jul-05 10 01-Sep-04 $82.00 820
000209 08-Jul-05 10 01-Sep-04 $83.00 830
000209 08-Jul-05 10 01-Sep-04 $83.00 830
000209 08-Jul-05 10 01-Sep-04 $82.00 820
000209 08-Jul-05 10 01-Sep-04 $221.00 2210
000209 08-Jul-05 10 01-Sep-04 $221.00 2210
000210 10-Oct-05 10 01-Sep-04 $82.00 820
000210 10-Oct-05 10 01-Sep-04 $83.00 830
000210 10-Oct-05 10 01-Sep-04 $83.00 830
000210 10-Oct-05 10 01-Sep-04 $82.00 820
000210 10-Oct-05 10 01-Sep-04 $221.00 2210
000210 10-Oct-05 10 01-Sep-04 $221.00 2210
000211 15-Oct-05 10 12-Oct-05 $100.00 1000
000212 16-Oct-05 10 12-Oct-05 $100.00 1000
000213 20-Oct-05 10 20-Oct-05 $200.00 2000
000214 25-Oct-05 10 20-Oct-05 $200.00 2000
000215 30-Oct-05 10 20-Oct-05 $200.00 2000
000216 10-Nov-05 10 20-Oct-05 $200.00 2000
000217 15-Nov-05 10 20-Oct-05 $200.00 2000

Some additional information:
The Reference Number is a unique identifier for that transaction, each
transaction is assigned a ReferenceNumber, a Craft Code, an Account
Code(straight time, overtime,etc), an Activity Date and other data. The
Craft Code, Activity Date & Account Code determine the Rate based on the
Effective Date of the corresponding AccountCode, CraftCode records in
tblCraftCodes(the rate table). I think that the query is getting all rates
that fit with the EffectiveDate regardless of Craft Code or Account Code.

How do I add the criteria tblCraftCodes.Account Code &
tblCraftCodes.CraftCode to only select a Rate and calculate an Amount once
for each transaction?

I hope that I gave you enough information. I really appreciate any advice
you can give me. Let me know if you need further information.
Thanks a lot,
JV

"Van T. Dinh" wrote:

I have involved in a similar topic recently. Check the sample download
provided by Doug in this discussion:

http://tinyurl.com/aa24k

--
HTH
Van T. Dinh
MVP (Access)


"Jason V" wrote in message
...
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.