Get Record Base on Selected Dates

[Benjamins via AccessMonster.com]

Hi,

I have 2 table.

Product:
PdtId PdtName PdtPrice PdtCurrency
1 AAA 5 USD
2 BBB 1000 JPY

Currency:
CurrId CurrCode CurrDate CurrRate
1 USD 01/01/08 1.18
2 JPY 01/06/08 76
3 USD 01/01/09 1.24
4 JPY 01/06/09 79
5 USD 01/01/10 1.35

Base on the date stated by the user, the system will return the
(Price*CurrRate) where CurrRate will be base on the latest date <= stated
date

Example 1
Date = 01/01/09
The result will be
AAA (5 * 1.24)
BBB (1000 * 76)

Example 1
Date = 01/07/09
The result will be
AAA (5 * 1.24)
BBB (1000 * 79)

Is there a query for this or i have to create more than 1 query in order to
get the result.

 

[Allen Browne]

See:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Tom Ellison explains how to create a query that returns the rate for the
date. You can then join this one to your Product table.

 

[Benjamins via AccessMonster.com]

Thank for yours info... will try it out.

See:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

Tom Ellison explains how to create a query that returns the rate for the
date. You can then join this one to your Product table.

Hi,

[quoted text clipped - 32 lines]

to
get the result.