Find Value from Maximum Field in MS ACCESS

%3CLINGO-SUB%20id%3D%22lingo-sub-1138994%22%20slang%3D%22en-US%22%3EFind%20Value%20from%20Maximum%20Field%20in%20MS%20ACCESS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138994%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20containing%203%20fields%2C%20say%20Field1%2C%20Field2%20and%20Field3.%20Want%20to%20make%20summary%20table%20where%20for%20every%20unique%20Field1%2C%20find%20the%20value%20of%20Field3%20when%20Field2%20is%20Maximum.%20Attached%20tables%20here.%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168002iD6CE248F3DCE2D2E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221.PNG%22%20title%3D%221.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1138994%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1144028%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20Value%20from%20Maximum%20Field%20in%20MS%20ACCESS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1144028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F538237%22%20target%3D%22_blank%22%3E%40digitalbhavik%3C%2FA%3EYou%20need%20two%20queries%20(one%20of%20which%20can%20be%20a%20subquery).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20Field1%2C%20MAX(Field2)%20as%20LatestDate%3C%2FP%3E%3CP%3EFROM%20tableA%3C%2FP%3E%3CP%3EGROUP%20BY%20Field1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESave%20that%20as%20%22LastDateperField1%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20a%20second%20query%2C%20join%20that%20first%20one%20back%20to%20the%20original%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20Field1%2C%20Field2%2C%20Field3%3C%2FP%3E%3CP%3EFROM%20tableA%20INNER%20JOIN%20LastDateperField1%20on%20tableA.Field1%20%3D%20LastDateperField1.Field1%20AND%20tableA.Field2%20%3D%20LastDateperField1.LatestDate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a table containing 3 fields, say Field1, Field2 and Field3. Want to make summary table where for every unique Field1, find the value of Field3 when Field2 is Maximum. Attached tables here. 1.PNG

 

1 Reply

@digitalbhavikYou need two queries (one of which can be a subquery).

 

First.

 

SELECT Field1, MAX(Field2) as LatestDate

FROM tableA

GROUP BY Field1

 

Save that as "LastDateperField1"

 

In a second query, join that first one back to the original table.

 

SELECT Field1, Field2, Field3

FROM tableA INNER JOIN LastDateperField1 on tableA.Field1 = LastDateperField1.Field1 AND tableA.Field2 = LastDateperField1.LatestDate