MS Access Query to get all column values for max date

%3CLINGO-SUB%20id%3D%22lingo-sub-1124069%22%20slang%3D%22en-US%22%3EMS%20Access%20Query%20to%20get%20all%20column%20values%20for%20max%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1124069%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20where%20we%20have%20multiple%20line%20items%26nbsp%3B%20that%20is%20user%20updates%20status%20on%20a%20daily%20basis%20till%20the%20work%20item%20is%20close%20where%20the%20word%20ID%20remains%20same%20only%20the%20status%20will%20change%20based%20on%20the%20progress%2C%20I%20am%20looking%20for%20a%20query%20where%20the%20latest%20date%20to%20be%20extracted%2C%20that%20is%20user%20may%20be%20on%20leave%20and%20had%20updated%20few%20days%20back%20then%20the%20latest%20what%20was%20updated%20should%20be%20extracted.%20Example%20below%3C%2FP%3E%3CP%3EW%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3EWork%20Id%3C%2FTD%3E%3CTD%3EStatus%3C%2FTD%3E%3CTD%3ELast%20Updated%20by%3C%2FTD%3E%3CTD%3EDate%20last%20updated%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EIn-Progress%3C%2FTD%3E%3CTD%3EABC%3C%2FTD%3E%3CTD%3E21-01-2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EIn-Progress%3C%2FTD%3E%3CTD%3EABC%3C%2FTD%3E%3CTD%3E20-01-2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3EIn-Progress%3C%2FTD%3E%3CTD%3EABC%3C%2FTD%3E%3CTD%3E19-01-2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EIn-Progress%3C%2FTD%3E%3CTD%3EXYZ%3C%2FTD%3E%3CTD%3E21-01-2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EIn-Progress%3C%2FTD%3E%3CTD%3EXYZ%3C%2FTD%3E%3CTD%3E20-01-2020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3EIn-Progress%3C%2FTD%3E%3CTD%3EXYZ%3C%2FTD%3E%3CTD%3E19-01-2020%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1124069%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1678285%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20Query%20to%20get%20all%20column%20values%20for%20max%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1678285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F529253%22%20target%3D%22_blank%22%3E%40Avinash_Visaji%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20query%20should%20give%20you%20the%20desired%20result%3A%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3ESELECT%20w.WorkID%20AS%20%5BWork%20ID%5D%2C%20%5Bw.Status%5D%20AS%20%5BWork%20status%5D%2C%20%5Bw.LastUpdatedBy%5D%20AS%20%5BLast%20Updated%20by%5D%2C%20%5Bw.DateLastUpdated%5D%20AS%20%5BDate%20last%20updated%5D%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EFROM%20WorkStatus%20AS%20w%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EINNER%20JOIN%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3E(%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3ESELECT%20ws.WorkID%2C%20MAX(%5Bws.DateLastUpdated%5D)%20AS%20Last%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EFROM%20WorkStatus%20AS%20ws%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EGROUP%20BY%20ws.WorkID%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3E)%20AS%20wl%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EON%20w.WorkID%20%3D%20wl.WorkID%20AND%20w.DateLastUpdated%20%3D%20wl.Last%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%3EORDER%20BY%20w.WorkID%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20called%20the%20table%20WorkStatus%2C%20but%20you%20can%20change%20it%20to%20yours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1679476%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20Query%20to%20get%20all%20column%20values%20for%20max%20date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1679476%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131546%22%20target%3D%22_blank%22%3E%40Tieme%20Woldman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20Tieme%2C%20much%20appreciated%20will%20give%20a%20try%20and%20try%20to%20make%20it%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 IdStatusLast Updated byDate last updated
1In-ProgressABC21-01-2020
1In-ProgressABC20-01-2020
1In-ProgressABC19-01-2020
2In-ProgressXYZ21-01-2020
2In-ProgressXYZ20-01-2020
2In-ProgressXYZ19-01-2020

 

 

2 Replies
Highlighted

@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

Highlighted

@Tieme Woldman 

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