Find most recent record by date and time

[Scott Whetsell, A.S. - WVSP]

I am trying to get a query to return the most recent record for each person
in the table.

Table fields include:
UL_UnitID
UL_Date
UL_Time
UL_STS

I need the query to return the most recent record for each unit based on the
date and the time. Using the 'max' option it will return the latest date or
the latest time. I cannot use the 'last' option because some updates may not
be done in correct order. I attempted to query the table first and create a
combined DateTime field of: [UL_Date] & " " & [UL_Time]. That will still not
correctly return the most recent record for both date and time.

Thanks,

 

[John Spencer]

You need to ADD the Date and Time together. Are UL_Date and UL_Time
DateTime fields? If so,

Field: Expr1: UL_Date + UL_Time

SELECT UL_UnitID
, Max(UL_Date + UL_Time) as Latest
FROM TableUL

SELECT TableUL.*
FROM TableUL INNER JOIN QueryAbove
On TableUL.UnitID = QueryAbove.UnitID
AND (TableUL.ULDate + TableUL.ULTime) = QueryAbove.Latest

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Scott Whetsell, A.S. - WVSP"

 

[Scott Whetsell, A.S. - WVSP]

Thank you, it was such an easy fix, and I should have thought of that. I
appreciate your time.

You need to ADD the Date and Time together. Are UL_Date and UL_Time
DateTime fields? If so,

Field: Expr1: UL_Date + UL_Time

SELECT UL_UnitID
, Max(UL_Date + UL_Time) as Latest
FROM TableUL

SELECT TableUL.*
FROM TableUL INNER JOIN QueryAbove
On TableUL.UnitID = QueryAbove.UnitID
AND (TableUL.ULDate + TableUL.ULTime) = QueryAbove.Latest

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Scott Whetsell, A.S. - WVSP"

I am trying to get a query to return the most recent record for each person
in the table.

Table fields include:
UL_UnitID
UL_Date
UL_Time
UL_STS

I need the query to return the most recent record for each unit based on
the
date and the time. Using the 'max' option it will return the latest date
or
the latest time. I cannot use the 'last' option because some updates may
not
be done in correct order. I attempted to query the table first and create
a
combined DateTime field of: [UL_Date] & " " & [UL_Time]. That will still
not
correctly return the most recent record for both date and time.

Thanks,