Return top record

[Gerald Stanley]

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.
.