max of date/time

I am trying to get the last record, example

Item # date time comment

125 10/28/2008 15:39 sent instructions
125 10/28/2008 16:43 cancelled instructions
125 03/05/2008 09:23 sent instructions
125 03/05/2008 10:45 cancelled instructions
125 11/28/2007 07:13 sent instructions

I only want the last record: ie 16:43 cancelled instructions

Any help would be appreciated.

Thank you

dchristo wrote:
I am trying to get the last record, example

Item # date time comment

125 10/28/2008 15:39 sent instructions
125 10/28/2008 16:43 cancelled
instructions 125 03/05/2008 09:23 sent
instructions 125 03/05/2008 10:45
cancelled instructions 125 11/28/2007 07:13
sent instructions

I only want the last record: ie 16:43 cancelled instructions

Any help would be appreciated.

SELECT Top 1 TableName.*
FROM TableName
ORDER BY [date] DESC, [time] DESC

Additional comments:
As "date" and "time" are reserved words you should not use them for field
names.

Date and time are (usually) better stored in one field rather than split up
into two.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I assume it’s the latest date/time per item number you want. With your
separate date and time columns you can include a correlated subquery in the
query's WHERE clause to return the latest date+time value:

SELECT *
FROM [YourTable] AS T1
WHERE [date]+[time] =
(SELECT MAX([date]+[time])
FROM YourTable as T2
WHERE T2.[Item #] = T1.[item #]);

If you want the latest date/time of all rows not per item number then omit
the correlation on the item # column form the subquery.

However you'd be better using a single column for the date and time. In
Access there is no such thing as a date value or time value per se, only a
date/time value. Your time column in fact is storing the time on 30 December
1899, which is day zero in Access's implementation of the date/time data
type, and your date column is storing the point of time at midnight at the
start of the day.

Also, date and time are best avoided as column names as these are the name
of built in functions.

Ken Sheridan
Stafford, England

"dchristo" wrote:

I am trying to get the last record, example

Item # date time comment

125 10/28/2008 15:39 sent instructions
125 10/28/2008 16:43 cancelled instructions
125 03/05/2008 09:23 sent instructions
125 03/05/2008 10:45 cancelled instructions
125 11/28/2007 07:13 sent instructions

I only want the last record: ie 16:43 cancelled instructions

Any help would be appreciated.

Thank you

Unfortunately, I do not have control of the fields they are named DATE_ENTER
and TIME_STAMP they come from tables pull from mainframe.



"Ken Sheridan" wrote:

I assume it’s the latest date/time per item number you want. With your
separate date and time columns you can include a correlated subquery in the
query's WHERE clause to return the latest date+time value:

SELECT *
FROM [YourTable] AS T1
WHERE [date]+[time] =
(SELECT MAX([date]+[time])
FROM YourTable as T2
WHERE T2.[Item #] = T1.[item #]);

If you want the latest date/time of all rows not per item number then omit
the correlation on the item # column form the subquery.

However you'd be better using a single column for the date and time. In
Access there is no such thing as a date value or time value per se, only a
date/time value. Your time column in fact is storing the time on 30 December
1899, which is day zero in Access's implementation of the date/time data
type, and your date column is storing the point of time at midnight at the
start of the day.

Also, date and time are best avoided as column names as these are the name
of built in functions.

Ken Sheridan
Stafford, England

"dchristo" wrote:

I am trying to get the last record, example

Item # date time comment

125 10/28/2008 15:39 sent instructions
125 10/28/2008 16:43 cancelled instructions
125 03/05/2008 09:23 sent instructions
125 03/05/2008 10:45 cancelled instructions
125 11/28/2007 07:13 sent instructions

I only want the last record: ie 16:43 cancelled instructions

Any help would be appreciated.

Thank you