Forum Discussion

digitalbhavik's avatar
digitalbhavik
Copper Contributor
Jan 30, 2020

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. 

 

access
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Jan 31, 2020

    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

     

     

Resources