Solved

Query for top 5 within every group

Posted on 2009-04-02
6
393 Views
Last Modified: 2013-11-28
I have a totals query with 3 fields: Region, Product, and AverageQty. I want a query that will only return the top 5 AverageQty within each region.

I can't recall ever using the drop down in a query to return the top 5. When I try it for this query I only get the top 5 records for a query. What else needs to be done to the query?
0
Comment
Question by:dlogan7
  • 3
6 Comments
 
LVL 3

Expert Comment

by:sreindl04
ID: 24054587
which database system you are working with?
0
 

Author Comment

by:dlogan7
ID: 24054618
I'm sorry. I thought I posted this to the Access section. I am using Access 2007.
0
 
LVL 3

Accepted Solution

by:
sreindl04 earned 250 total points
ID: 24054796
I was thinking on hierachies in oracle sqlplus but in Access... Use VBA and SELECT TOP might help...

The only idea I have is using unions if you know the regions in advance (see sql example)
SELECT TOP 3 Region, Product, AvgQuantity 
FROM Tabelle1
WHERE region = 1
ORDER BY 3 DESC
UNION
SELECT TOP 3 Region, Product, AvgQuantity
FROM Tabelle1
WHERE region = 2
ORDER BY 3 DESC

Open in new window

0
 
LVL 12

Assisted Solution

by:koutny
koutny earned 250 total points
ID: 24058168
I don't know whether a solution is still required but the query might look something like the code snippet below.
The only problem with that query would be that in case there would be several products with the same averages then the query would show them all - it basically shows all products for each region with averages in the top 5.

I don't know where you are using this query but if you are trying to produce a report then there are other ways of achieving this (subreports).
SELECT R.RegionID, R.RegionName, RA.Product, RA.AverageQty
FROM Regions R INNER JOIN queryRegionAverages RA 
ON R.RegionID = RA.RegionID
 
WHERE 
RA.AverageQty in (SELECT TOP 5 AverageQty FROM queryRegionAverages RA2 WHERE RA.RegionID = RA2.RegionID ORDER BY RA2.queryRegionAverages DESC)
 
ORDER BY R.RegionID, RA.AverageQty DESC

Open in new window

0
 
LVL 3

Expert Comment

by:sreindl04
ID: 24068836
The point with the "same" averages prevented me from providing an sql with subselects. You might work with subsubselects but I do not know if access supports this kind of queries and the performance impact might be high. Access does not scale very well with subselects especially when working on shares.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Passing variables to a function 6 36
Exporting Access Tables as CSV 3 24
Microsoft Access - Stopping Control - (minus) 4 32
Sum with where criteria on a report 5 16
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
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.

810 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