asked on

MS Access find Top Value with group and associated record data

HI Everyone,

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
tbl1.item
tbl2.Perc (top value in group by Item)
tbl2.CCode

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?

Thanks,

MC

Rey Obrero (Capricorn1)

select T.*
from tbl2 as T
inner join
(select max(t2.perc) as MaxPerc, item from tb2 t2 group by t2.item) as t1
on t.perc=t1.maxPerc and t.item=t1.item

MCaliebe

ASKER

Thanks for the help Cap, but I coudln't get it to work. I enclosed a sample DB for you to look at.

My query results should be
Table1.Item_Number (all records)

Join to Table2.Item_Number

Table2.%ofTot(max number based on group of all like item_numbers)
Table2.C_Code
sample.accdb

Rey Obrero (Capricorn1)

run query2
sampleRev.accdb

Rey Obrero (Capricorn1)

better use this, run query3
sampleRev.accdb

MCaliebe

ASKER

Query 2 appears to be doing fine for me.

Can you take a min to explain what your doing here? If you know of an article worth reading on constructing these temp queries, I'd appreciate it.

I'm using this one
SELECT T.*
FROM table2 AS T
INNER JOIN
(select max(t2.[%ofTot]) as MaxTot, item_number from table2 t2 group by t2.item_number) AS t1
ON (T.item_number = t1.item_number) AND (T.[%ofTot] = t1.maxTot);

Query 3 seems to be even more complicated.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MCaliebe

ASKER

Thanks for the additional information!