Linking Date Based Data

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

.

Relevant Pages

  • Re: How to receive a buck from the Net?(Was: Re: 1 dollar free ... SPAM)
    ... Any transaction system I know will leave you with just about 50 ... cents or less making such tiny transactions a nuisance. ... of sites will ask for 0.10 USD or so to read a page. ... stuff like this is going to happen as soon as micropayments happen. ...
    (alt.internet.search-engines)
  • Re: How to receive a buck from the Net?(Was: Re: 1 dollar free ... SPAM)
    ... short of someone actually writing you a personal check for a ... Any transaction system I know will leave you with just about 50 ... cents or less making such tiny transactions a nuisance. ... of sites will ask for 0.10 USD or so to read a page. ...
    (alt.internet.search-engines)
  • Re: How to receive a buck from the Net?(Was: Re: 1 dollar free ... SPAM)
    ... short of someone actually writing you a personal check for a ... Any transaction system I know will leave you with just about 50 ... cents or less making such tiny transactions a nuisance. ... of sites will ask for 0.10 USD or so to read a page. ...
    (alt.internet.search-engines)
  • Problem with date based query
    ... I have two tables - Transactions and Rates. ... Refce TxnDate Curr Amount ... 10 21/03/2006 USD 10.87 ... Can this be done in a *single* Query? ...
    (comp.databases.ms-access)