Find Value from Maximum Field in MS ACCESS

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




SELECT Field1, MAX(Field2) as LatestDate

FROM tableA



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