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
``````
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:
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
Commented:
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.
Commented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Chief Technology OfficerCommented:
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. :)
Author Commented:
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
``````
Chief Technology OfficerCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.