Solved

MS Access find Top Value with group and associated record data

Posted on 2013-01-15
7
289 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 38779946
run query2
sampleRev.accdb
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now