MS Access Query to get all column values for max date

[Avinash_Visaji]

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 on the progress, I am looking for a query where the latest date to be extracted, that is user may be on leave and had updated few days back then the latest what was updated should be extracted. Example below

W

Work Id	Status	Last Updated by	Date last updated
1	In-Progress	ABC	21-01-2020
1	In-Progress	ABC	20-01-2020
1	In-Progress	ABC	19-01-2020
2	In-Progress	XYZ	21-01-2020
2	In-Progress	XYZ	20-01-2020
2	In-Progress	XYZ	19-01-2020

[#2]

@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

[#3]

@Tieme Woldman 

Thanks Tieme, much appreciated will give a try and try to make it work.