Solved

MS Access find Top Value with group and associated record data

Posted on 2013-01-15
7
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 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