Query the top top ten by category?

[WildlyHarry]

I have a table that has a list of occurences by store locations. I use a
query to count those occurences and then produce a top ten report. I am
currently producing that report by market. In order to do this I have built
independent queries for each market, which are the source for subreports,
that are all on the same report. In effect I get one report with the top ten
occurences by market. I now need to move to a regional report. The issue I
have is that there are 70 regions. And I produce three unique reports. I
only have 6 markets so I had no real issue producing 18 different queries to
feed the subreports. Now however, I would need 210 queries and subreports,
which is probably going to bump me up against a size limitation as well as
not having the time to right all of that. Is there a way that I can write a
query that will give me the top ten count for each issue for each region by
my market without having to write a seperate query for each region? An
example of my source table is below. Thanks in advance for your help.

Source Table:

[Location], [Issue], [Region], [Market]
Location 1, Issue 1, Region 1, Market 1
Location 1, Issue 2, Region 1, Market 1
Location 2, Issue 4, Region 7, Market 5
....

There are multiple locations and multiple records per location as it is
possible to have up to 150 different [Issues]. I write queries that count
the number of times an [Issue] appears by [Market]. Then I take the top ten
from that query to feed the subreports for each [Market]. I need to
ultimately end up with a top ten by [Region] by [Market].

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[KARL DEWEY]

Use a ranking by group query like this one ---
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Change the table and field names to yours. Add the second grouping field
and criteria on rank for <=10.