Solved

Access Top n in group

Posted on 2010-08-13
7
786 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
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
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 77

Accepted Solution

by:
peter57r earned 250 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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 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 59

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

762 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

20 Experts available now in Live!

Get 1:1 Help Now