Currency Rates

[Samos]

I have a file "Currency Rates" which contains three fields: Currency, Rate, Starting Date. It looks approximately so:

EUR 1,5 01-Jan-2004

US 1 01-Jan-2004

EUR 1,4 01-Mar-2004

EUR 1,6 05-Jun-2004

...

There Is other file "Price-List". Fields:

ItemKey,

NativePrice,

Rate Date,

Currency,

Rate,

Currency Price (=NativePrice*Rate).

Currency are related. I want, that in this file at a choice of currency at me the prices at the rate from a file " Currency rates " were recalculated. It not a problem.

But, I as want, that I could receive a PriceList for any date. I.e., if I choose for currency EUR date 10-Mar-2004 the relation formula must return 1,4... And for date 12-Feb-2004 the rate would be 1,5. If date 01-Jul-2004 - rate is 1,6

How?

[Ender]

Maybe have Rate use a lookup by a relationship based on a concatenated key: Currency_RateDate, where Currency_RateDate is a calc with text result = Currency & " " & Rate Date.

Set the Rate lookup to use the related Rate in Currency Rate, and if no exact match, use next lower.