Solved

Query for top 5 within every group

Posted on 2009-04-02
6
391 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

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…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

746 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

11 Experts available now in Live!

Get 1:1 Help Now