how to select order status history?

[James]

how can i select all orders and group by their status history i.e. order
received, processing, completed BUT, if there is a duplicate status, i only
want it to show the most current status. i have an order table, a statuslog
table. this is ms access. i have pasted some sql i use to return the
latest status by using max in a nested select but how can i show all
statuses for an order but only show the most recent status if theres a dup
status?

SELECT sl.OrderID, sl.Status
FROM Statuslog AS sl
WHERE (((sl.Date)=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)));

 

[MGFoster]

how can i select all orders and group by their status history i.e. order
received, processing, completed BUT, if there is a duplicate status, i only
want it to show the most current status. i have an order table, a statuslog
table. this is ms access. i have pasted some sql i use to return the
latest status by using max in a nested select but how can i show all
statuses for an order but only show the most recent status if theres a dup
status?

SELECT sl.OrderID, sl.Status
FROM Statuslog AS sl
WHERE (((sl.Date)=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Perhaps this:

SELECT sl.OrderID, sl.Status
FROM Statuslog AS sl
WHERE sl.Date=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)
GROUP BY sl.OrderID, sl.Status
HAVING Count(sl.Status) = 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt6udoechKqOuFEgEQKPHACeO901D7KPtR9YmSAhleE/P7+hfn8An32l
1SPUNIktlVuBUXzX0xapAsuH
=r81e
-----END PGP SIGNATURE-----

 

[James]

thank you for your response. How can show the status dates of differnt
types of statuses. i.e. i want it to return the record like this:

OrderID, CurrentStatus, DeliveredDate,CompletedDate

 

[James]

basicly, my question as changed from the first one. insted of grouping i
want to show the order statuses on one record, but only the Currrent status,
DeliveredDate, CompletedDate.

 

[James]

nm, i figured it out. i just needed to put the nested selects as columns
i.e.

SELECT sl.OrderID AS OrderID, sl.Status AS Status, sl.Date, (select
max(s.date) as MaxOfDate from statuslog as s where s.status = "Completed"
and s.OrderID = sl.OrderID) as CompletedDate, (select max(s.date) as
MaxOfDate from statuslog as s where s.status = "Delivered" and s.OrderID =
sl.OrderID) as DeliveredDate
FROM Statuslog AS sl
WHERE (((sl.Date)=(SELECT Max(sl2.date)
FROM statuslog As sl2
WHERE sl2.orderid = sl.orderid)));