Access query

[Nathan]

I have a database containing a linked excel sheet, as a table and
am trying to sort the data and am unsure how to do it.

The data concerned is a list of user logins to a server and I want to be
able to
output only the most recent log.

There are multiple records of the same users and a field stating the
date and a separate field stating the time of their last access.
Is there a way of maybe querying the data to filter the records and only
display one record for each user which was their most recent log. This would
have
to be searched in relation to the date as one field and against the time as
another field.
Any help is much appreciated.

 

[Ken Snell \(MVP\)]

Because you've separated the date and time into separate fields, your query
is a bit more complicated to do. (Note: there is no need to separate date
and time into separate fields. You should use a single field to hold the
date and time -- the DateTime field does this for you. Then you'd not need
to recombine the data back into a single value, as I do in the query below)
in order to get a chronological value to be used for sorting.)

This should work if you replace my generic names with your real table and
field names.

SELECT *
FROM LinkedTablename
WHERE LastAccessDate + LastAccessTime =
(SELECT Max(T.LastAccessDate + T.LastAccessTime)
FROM LinkedTablename AS T
WHERE T.UserName = LinkedTablename.UserName)

 

[Nathan]

Thanks very much for that. Thats brilliant.

Because you've separated the date and time into separate fields, your query
is a bit more complicated to do. (Note: there is no need to separate date
and time into separate fields. You should use a single field to hold the
date and time -- the DateTime field does this for you. Then you'd not need
to recombine the data back into a single value, as I do in the query below)
in order to get a chronological value to be used for sorting.)

This should work if you replace my generic names with your real table and
field names.

SELECT *
FROM LinkedTablename
WHERE LastAccessDate + LastAccessTime =
(SELECT Max(T.LastAccessDate + T.LastAccessTime)
FROM LinkedTablename AS T
WHERE T.UserName = LinkedTablename.UserName)