Selecting MAX(date) from multiple rows

hi, I have an sql statement returning the rows of a table based on a date. The trouble is, it is returning duplicated rows (transactionID's with multiple dates) when I need it to return only the row with the Maximum date for each transactionID.
This is the code I am using:

SELECT *
FROM tblExchRates RIGHT JOIN tblTransactions ON tblExchRates.currency=tblTransactions.currency
WHERE (((tblExchRates.Date)<=tblTransactions.Date) And ((tblExchRates.currency)=tblTransactions.currency));

Thanks all.
Jen

First off, I don't totally get this...I'm not saying it's wrong, but I think I'm confused...maybe you or someone can clarify if there is value in the following...take a look @ your outer join and then your Where clause...

...if I understand correctly, you're basically making the outer join obselete. You want every record from the tblTransactions table, whether there's a match on both table's currency field or not, but then in your WHERE clause you're limiting the return rows where you want both currecy fields to match...
(tblExchRates.currency = tblTransactions.currency)
...you're basically taking an outer join and making it an inner join through more logic...just re-write your query to use an inner join instead of an outer join...@least that's what I would do. If there's other value in having that outer join for some reason, then keep it as is I guess.

Now...the max Date. I don't totally understand...could you post the 2 tables that you're joining...the fields and how they're related, then what fields you want in the output? I took it that you want the Transaction ID and the max(transactionDate) returned...for that you don't need a join @ all...I'm a little confused. We got your solution though, just need some clarifcation.

ok thanks for ur reply..
Let me go back..
The 2 tbles I am concerned with are: tblTransactions and tblExchangeRates

tblTransactions has:
transactID autoNumber
Date date of transaction
expCategory selected from a combo box on tblCategory
transCurrency selected from a combo box on tblLocation
transAmt cost of transaction
exchRate $amount from tblExchangeRate
AUD$ transAmt converted to AUD$ based on
exchRate


tblExchangeRate has:
exRateID autoNumber
exRateDate date of exchange rate
exAmount amount of exchange rate
exCurrency selected from tblLocation
(ie Rupee, Baht, US$...etc)

I want to create a form to add transactions (based on tblTransactions) and have the "exchRate" automatically entered based on the transaction Date.

The tblExchangeRate can be missing dates for a particular currency, so the transaction table needs to select the latest date in the tblExchangeRate to retrieve "exAmount" where the tlbTransaction.currency = tblExchangeRate.currency.

whew, ok, I hope I have explained that well enough.

Thanks again.