dates in query

[Guest]

Hello. How could I select the record with the latest date from a group of
records?
Lets say there are multiple records for a single order with specific time
stamps for order status. I only want to return the latest time stamp. thanks.

 

[David Ferguson]

Try the following SQL


SELECT OrderTable.OrderNo, Max(OrderTable.TimeStamp) AS MaxOfTimeStamp
FROM OrderTable
GROUP BY OrderTable.OrderNo;

 

[Michel Walsh]

Hi,


Take a look at http://www.mvps.org/access/queries/qry0020.htm.


Hoping it may help
Vanderghast, Access MVP

 

[Guest]

hi
write a query that select the order number and the max
date from your table.
SELECT tbl.ordernum, Max(tbl.Date) AS MaxOfDate
FROM tbl
GROUP BY tbl.ordernum
HAVING (((tbl.ordernum)="somthing"));
then use this query in a second query that select all the
other data
join query ordernum on table orderumn and query maxofdate
on table date.
SELECT qrytest.ordernum, qrytest.MaxOfDate, tbl.otherdata
FROM tbl INNER JOIN qrytest ON (tbl.Date =
qryctest.MaxOfDate) AND (tbl.ordernum = qrytest.ordernum)
GROUP BY qrytest.ordernum, qrytest.MaxOfDate,
tbl.otherdata;
tested. works.