Solved

MS Access find Top Value with group and associated record data

Posted on 2013-01-15
7
293 Views
Last Modified: 2013-01-16
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
0
Comment
Question by:MCaliebe
  • 4
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38779690
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
0
 

Author Comment

by:MCaliebe
ID: 38779905
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38779946
run query2
sampleRev.accdb
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38779976
better use this, run query3
sampleRev.accdb
0
 

Author Comment

by:MCaliebe
ID: 38782376
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.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 38782667
this part
SELECT T.*
FROM table2 AS T


just select everything from table2 and give it an alias T (T contains all records from table2 in memory)


this part, a sub query

(select max(t2.[%ofTot]) as MaxTot, item_number from  table2 t2 group by t2.item_number)  AS t1

gets the maximum value of [%ofTot] and grouped by item_number  and was given an alias of t2 (t2 contains the records from table2 using a Max and group by aggregates in memory)

then the two set of recordset were join on this line

ON (T.item_number = t1.item_number) AND (T.[%ofTot] = t1.maxTot);


it is just like creating a two tables and creating a query that joins the two table using two fields
0
 

Author Closing Comment

by:MCaliebe
ID: 38782696
Thanks for the additional information!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question