?
Solved

Access Top n in group

Posted on 2010-08-13
7
Medium Priority
?
793 Views
Last Modified: 2013-11-29
I have a table where I need to get the top 25 highest amount items.
I have a query that has all the items and a count column. Having a hard time making a subquery to work.

Ok, Let's say that I have already the following query:

Category   Item      InventoryCount
Beverage   milk		3
Beverage   water	2
Beverage   beer		9
Utensil    fork		7
Utensil    spoon	2
Utensil    knife	1
Utensil    spork	4

My desired output is the highest Inventory of the topmost 2 Categories.

Category   Item      InventoryCount
Beverage   beer		9
Beverage   milk		3
Utensil    fork		7
Utensil    spork	4

Open in new window

0
Comment
Question by:Papote
[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
7 Comments
 
LVL 3

Expert Comment

by:jnbkze
ID: 33435317
I am not a programmer by any means.
ok, so my idea would be something like this.

search for values, using If statements. So if one number is > than another, then see if that number is higher than another, and anothe and another, untill it is lower than one number. then shift all values in the array of valued 1-25 (of which this value is higher) , down one. starting at the lowest value. drop it off, and make 2=1 3=2 4=3 5=4 .. untill you have shifted them all down, and then enter your current value into the slot where it is lower than another but higher than the others which were shifted down.

I can just see the lines of code for this be pages and pages long, thats why I have no interest in programming :) I`m sur ethere are better methods out there.. perhaps something like a pivot table ? idk, its just a thought. I hope someone else helps you with something easier! :)

good luck!
Edited by WaM 8-14-10
0
 
LVL 4

Expert Comment

by:jennynover
ID: 33435363
in the Query Design window, R.Click in the gray area and select Properties.   There you will see a "Top Values" property.  You put 25 in there.   See also http://www.techonthenet.com/access/queries/top_results.php for detailed instructions.

There you go!
topN.png
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 33435444
As jennynover has suggested, you can use TOP to limit the number of records returned. You also need to specify the "Order By" as "InventoryCount DESC" to return the highest amounts.
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 33435499
I don't think your question is very clear.  You ask for a different result in the first line to the result you ask for in your example.

I am assuming you want the top 2 items for each category as shown in your example.

Getting the top N per group is a little more involved...put your own tablename in..

Select T.* from tablename as T where T.[inventorycount] in (Select top 2 [inventorycount] from tablename where [category]= T.[category] order by [inventorycount] desc)
order by T.[category], T.[inventorycount] desc
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 1000 total points
ID: 33436929
Agree with peter57r. That solution should work for say top 5 also but keep in mind that matching inventory counts will come back together like what happens in a TOP WITH TIES query in T-SQL (i.e., you may get 3 or more records even though you ask for top 2) -- this is often desired. If not, then you can try a ranking approach and in a query similar to what peter57r shows you additionally look at item name in relation to other item names (alphabetic sort) so that on matches you chose the item name coming first alphabetically to break tie and assign lower rank ...

Analytical SQL : Where do you rank? - http:A_1555.html

Start with peter57r's approach though and as you need to expand it and take consideration of other factors I mentioned, then please have a read and hopefully it will serve you as well as it does me. :)
0
 

Author Comment

by:Papote
ID: 33438445
peter57r: I get a data type mistmatch error.

The code below works to accomplish what I want, but I also need to group by year and location the 2 topmost categories.
SELECT  a.item, 
        a.category, 
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, 
          a.item, 
          a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.category, COUNT(*) DES

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33438463
That is pretty much what the ranking solution is by the way. As such you may want to read the article as it may help you with adding in additional ranking criteria doing it the way shown versus in the outer query. There is not a year nor a location in the data you showed, so not sure how we may be able to help with that.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

752 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