Forum Discussion
digitalbhavik
Jan 30, 2020Copper Contributor
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.
- George_HepworthSilver Contributor
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