SQL Puzzler

[GJT]

I have 2 tables. The main table holds currencies info and that to which it is linked contains a historical list of these exchange rate values and when they were updated.

What I need to do is ONLY return the current exchange rates i.e.

• recent (most recently updated - to todays date)
• DISTINCT exchange rate items

Any ideas????

Thanks for your help.

 

[Rich]

Look up Max, there have been numerous examples posted here for use in SQL, or a simple DMax on the date field

 

[GJT]

Rich

I have tried to do this without success.

SELECT DISTINCT CURRENCY_HISTORY.CurrencyId, CURRENCY.ShortCurrency, Max(CURRENCY_HISTORY.RateRevised) AS Revised
FROM [CURRENCY] INNER JOIN CURRENCY_HISTORY ON CURRENCY.Id = CURRENCY_HISTORY.CurrencyId
GROUP BY CURRENCY_HISTORY.CurrencyId, CURRENCY.ShortCurrency
ORDER BY CURRENCY_HISTORY.CurrencyId;

this returns the curreny desc, and revision date as expected but as soon as I add the exchange rate to the recordset I get multiple entries returned.....this is not what I want......

 

[Rich]

Is the Exchange rate linked by CurrencyID

 

[GJT]

Thanks for your help - you were right, in the anals of this Database I found the SQL answer I was looking for. 2 inner joins were required.

Thanks again