Linking Date Based Data

[dave]

I have two tables - Transactions and Rates.

Table: Txns
Refce TxnDate Curr Amount
10 21/03/2006 USD 10.87
11 10/04/2006 USD 20.00

Table: Rates
Curr RateDate Rate
EUR 01/01/2006 1.41
USD 01/01/2006 1.80
USD 01/03/2006 1.81
USD 01/04/2006 1.82
USD 01/05/2006 1.83

I want to link these tables in a Query so that I can value the
transactions using the currency rate in force at the transaction date.
In this example, for Refce=10, the rate should be 1.81; for Refce=11,
it should be 1.82.

If only a forward dated rate is found, it should be ignored.

All dates are ddmmyyyy

Can this be done in a single Query?
Can anyone help with the SQL please?

Thanks
Dave

 

[Guest]

Try this with the first query named TxnsRateDate --

SELECT Txns.Refce, Txns.TxnDate, Txns.Curr, Txns.Amount, Max(Rates.RateDate)
AS MaxOfRateDate
FROM Txns INNER JOIN Rates ON Txns.Curr = Rates.Curr
WHERE (((Rates.RateDate)<=[TxnDate]))
GROUP BY Txns.Refce, Txns.TxnDate, Txns.Curr, Txns.Amount;

SELECT TxnsRateDate.Refce, TxnsRateDate.TxnDate, TxnsRateDate.Curr,
TxnsRateDate.Amount, Rates.Rate
FROM Rates INNER JOIN TxnsRateDate ON (Rates.Curr = TxnsRateDate.Curr) AND
(Rates.RateDate = TxnsRateDate.MaxOfRateDate);

 

[dave]

Thanks Karl

I had been hoping for a single Query, but I guess that's not possible
given that I need the rate associated with the latest rate date.

Dave