Latest record 1

[mrkuosea]

I am trying to write a simple Access 2007 query that returns only the latest record based on the date from a table that contains name of person, date and time of contacting the person.

I have tried Max and Where <=Now() but even with these conditions running the query returns all the records in the table.

Is there a way to get just the record with the latest date?

Thanks,
Mark.

 

[PHV]

A starting point:
SELECT * FROM yourTable
WHERE DateTimeField = (SELECT Max(DateTimeField) FROM yourTable)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkuosea]

Thanks for your help PH but I forgot to mention in my initial question that I needed the simple query to return only the latest record based on the time and date not just the date.

I tried to use your solution with time but it returns all records for the date. To return only one record the query needs to return the latest record based on date and time.

Sorry for the lack of detail.

Is there a way to incorporate time in the query? I have separate date and time fields in my table.

Thanks,
Mark.

 

[PHV]

One way:
SELECT * FROM yourTable
WHERE DateField = (SELECT Max(DateField) FROM yourTable)
AND TimeField =(SELECT Max(TimeField) FROM yourTable WHERE DateField = (SELECT Max(DateField) FROM yourTable))

Another way:
SELECT * FROM yourTable
WHERE (DateField + TimeField) = (SELECT Max(DateField+TimeField) FROM yourTable)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mrkuosea]

Thanks PH! It worked!