Find Value from Maximum Field in MS ACCESS

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

@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