Query - returning latest date/time from multiple records

[IanB]

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

 

[Ken Snell [MVP]]

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

 

[IanB]

Ken

Thats great - thanks for your help

Ian B

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