Need help on an query

[Tim]

Hi folks,

I have two tables:Sales and Currencies.

Sales table has the following fields:
Invoice Code Amount Date
1 001 10 1/1/04
2 003 15 8/31/03
3 005 20 9/1/03

Currencies table has the following fields:
Code EffectDate Rate
001 7/1/03 1.5
001 12/1/03 1.6
001 1/6/04 1.7
003 9/1/03 1.2
005 8/1/03 1.3
005 9/13/03 1.4
005 1/1/04 1.3

The following is the output that I am looking for:
Invoice Code Amount Date TotalAmount
1 001 10 1/1/04 16
2 003 15 8/31/03 0 'prior effect date 9/1/03
3 005 20 9/1/03 26

Could anyone show me how to write a quey to have the
above output?

Any help will be appreciated.

Thanks in advance.

Tim.

 

[Allen Browne]

Use a subquery to get the most recent Rate from the Currencies table.

Your query needs only the Sales table. In the Field row of query design
view, enter something like this into a fresh column:

Rate: ( SELECT TOP 1 Rate FROM Currencies
WHERE Currencies.EffectDate < Sales.Date
ORDER BY Currencies.EffectDate DESC, Currencies.Code DESC )

If you are tempted to use DLookup() instead, it won't work because you
cannot specify the descending order to get the most recent match. There is a
replacement for DLookup() that does allow this. Copy from:
http://allenbrowne.com/ser-42.html

BTW, if you really do have a field named "Date", consider changing the name
and all references throughout your database. Date is a reserved word in VBA
code (for the system date). Sooner or later it will bite you and understand
the system date where you intended the value of your field.

 

[Tim]

Allen,

Thanks for your help.

It works great.

Tim.