Query - returning latest date/time from multiple records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

IanB - 09 Nov 2005 04:12 GMT

I have a mental block!

I have a table with 2 fields.
A location field (text 4)
A date/time stamp field (Date/Time)
I can have multiple records for the same location.
I need to extract from all the location fields, only 1 record per location,
that being the newest date/time.

I can do this in code but there must be an SQL selection that will produce
the same result!
Any help apreciated

TIA

Ian B

Reply to this Message

Ken Snell [MVP] - 09 Nov 2005 04:26 GMT

SELECT * FROM Tablename
WHERE Tablename.DateTimeField =
(SELECT Max(T.DateTimeField)
FROM Tablename AS T
WHERE T.LocationField = Tablename.LocationField);

Signature

Ken Snell
<MS ACCESS MVP>

>I have a mental block!
>
[quoted text clipped - 13 lines]
>
> Ian B

Reply to this Message

IanB - 09 Nov 2005 04:44 GMT

Ken

Thats great - thanks for your help

Ian B

> SELECT * FROM Tablename
> WHERE Tablename.DateTimeField =
[quoted text clipped - 24 lines]
> >
> > Ian B

Reply to this Message