Return top record

I am tracking river levels for three rivers. I have set up a query to give me the latest reading for each river. However, it is giving me the first readings, not the last.
Here is what I have:

SELECT TOP 3 tblriverlevel.ID, tblriverlevel.River, tblriverlevel.Date, tblriverlevel.[Levelft], tblriverlevel.[Levelin], tblriverlevel.Remarks, tblriverlevel.EventName
FROM tblriverlevel
WHERE (((tblriverlevel.Date) In (SELECT TOP 1
[Self].[date]
FROM
[tblriverlevel] AS [Self]
WHERE
[Self].[river] = [tblriverlevel].[river])))
ORDER BY tblriverlevel.Date DESC;

I am not sure what I am doing wrong.

It looks as if the ORDER BY clause is applied to the first
SELECT not the second. You could also use the Max function
e.g.
WHERE (((tblriverlevel.Date) In (SELECT Max([Self].[date])
FROM
[tblriverlevel] AS [Self]
WHERE
[Self].[river] = [tblriverlevel].[river])))


Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I am tracking river levels for three rivers. I have set
up a query to give me the latest reading for each river.
However, it is giving me the first readings, not the last.
Here is what I have:

SELECT TOP 3 tblriverlevel.ID, tblriverlevel.River,
tblriverlevel.Date, tblriverlevel.[Levelft],
tblriverlevel.[Levelin], tblriverlevel.Remarks,
tblriverlevel.EventName
FROM tblriverlevel
WHERE (((tblriverlevel.Date) In (SELECT TOP 1
[Self].[date]
FROM
[tblriverlevel] AS [Self]
WHERE
[Self].[river] = [tblriverlevel].[river])))
ORDER BY tblriverlevel.Date DESC;

I am not sure what I am doing wrong.
.

That worked great. Thank you.