Forum Discussion
MS Access Query to get all column values for max date
Hi All, I have a table where we have multiple line items that is user updates status on a daily basis till the work item is close where the word ID remains same only the status will change based o...
This query should give you the desired result:
SELECT w.WorkID AS [Work ID], [w.Status] AS [Work status], [w.LastUpdatedBy] AS [Last Updated by], [w.DateLastUpdated] AS [Date last updated]
FROM WorkStatus AS w
INNER JOIN
(
SELECT ws.WorkID, MAX([ws.DateLastUpdated]) AS Last
FROM WorkStatus AS ws
GROUP BY ws.WorkID
) AS wl
ON w.WorkID = wl.WorkID AND w.DateLastUpdated = wl.Last
ORDER BY w.WorkID
I called the table WorkStatus, but you can change it to yours.
Hope this works for you.
Best wishes,
Tieme
Thanks Tieme, much appreciated will give a try and try to make it work.
