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...

access

Woldman

Iron Contributor

Sep 17, 2020

Avinash_Visaji

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