Solved

MS Access find Top Value with group and associated record data

Posted on 2013-01-15
7
288 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

21 Experts available now in Live!

Get 1:1 Help Now