Look up a value in a table

[naungsai]

Dear Friends

I have a table which contains Date and Cost.

ADate Cost
1/4/2011  100500
1/27/2011  23000
1/29/2011  50000
2/3/2011   30000

Another Table have Exchange Rates

EDate   Rate
1/1/2011  700
1/14/2011  780
1/25/2011  760
2/2/2011  740

The cost in the first table is in Local currency. The rate in second table is Unit of Local currency per one US$.
I want to convert the value in cost column of first table with the respective exchange Rate according to changes in dates of both table.

How should I look up the value?

Thanks in advance!

Sai

 

[jzwp22]

You will need a query that uses a subquery to get the applicable rate based on the dates. Something along these lines should work.

SELECT tblCosts.Adate, tblCosts.cost, (Select top 1 Rate from tblRates where edate<=adate Order by edate desc) as ApplicableRate
FROM tblCosts;

 

[naungsai]

Dear Friend

Thank you very much. It does work.
Your prompt response help me a lot.

Sai Kyaw Han

 

[jzwp22]

You're welcome.