Solved

MS Access find Top Value with group and associated record data

Posted on 2013-01-15
7
291 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
Back Up Your Microsoft Windows Server®

Back up 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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hit enter key to run macro 13 23
Dynamically Reorder List Box 4 38
ms access filter query with empty combobox 5 29
Dcount help 2 12
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

770 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