Forum Discussion
Find Value from Maximum Field in MS ACCESS
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. ...
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