Select query to display records closest to today's date

[Sean Skallerud]

I have an ODBC connection to a table that has a date field associated with an
ID. The problem is the software creates multiple records everytime a
schedule is created or updated. So one person can have 5 or 10 different
records.

I need some help creating a query that will pull out 1 record per person per
day based on the creation date (which contains both the date and time) and
the date the query is ran.

I've tried using the Top 1 function but I'm probably using it wrong since
only 1 record per day shows up.

Any help is very much appreciated.

 

[Ken Snell \(MVP\)]

If you don't care which record, use Max function to get a single record:

SELECT T.*
FROM TableName AS T
WHERE T.CreationDate =
(SELECT Max(TT.CreationDate)
FROM TableName AS TT
WHERE TT.ID = T.ID AND
DateValue(TT.CreationDate) = Date());