MS Access find Top Value with group and associated record data
Posted on 2013-01-15
I have a query to construct in which I have two tables, tbl 1 has two fileds, both unique, ID and Item. Table 2 has four fields, ID, Item, Perc, CCode.
The two tables have a one to many relationship on the Item field
tbl1.item(unique) = tbl2.item(multiple)
I need to query for
tbl2.Perc (top value in group by Item)
I can get the Item and the Top 1 Perc by using MAX, however if I want to also see the corresponding CCode for the max record, my query expands to allow more records. I tried using FIRST for my CCode, but that too brought in mixed data.
Any thoughts on how I accomplish this?