Top by Market Query

[Guest]

Hello, I have database that tracks issues at my stores. I am trying to write
a query that will allow me to view the top ten issues in each region. I am
querying a table with all the issues. Using the count function to provide
the total for each issue. I have set the query to return the top ten records
but it will only do so for one region. I have 7. Any ideas on how to
accomplish this?

 

[Guest]

I am guessing that in your query that you now group by region.....eliminate
that - so that it is all one group....and I think you have your solution.

 

[Guest]

I group by region because I need to know the top 10 for each region
individually. Right now it only gives me the top ten for the first region
listed in my table. Hence, the problem.

 

[Guest]

ah - I misunderstood your question. thought you wanted an overall top 10...

well 1 quick & dirty method is to copy paste that query 6 more times and
enter into each one a unique region ID criteria - so that each of the seven
queries is reporting on one different region.

now that idea makes the sql purist in me shudder - but it would be pretty
darn quick and easy....

then make a blank unbound report - and plug in the seven sub reports....

sorry not to offer something more elegant off the cuff.....

 

[Guest]

ah - misunderstood the question - thought you wanted a single top ten

well one quick & dirty method is to reuse that query...copy/paste it six
times and enter a different unique region ID into each for criteria so each
query is working only on one region - - so the end result is you get the top
10 for all 7 regions via 1 query each...

then reassemble into one report by making a single blank unbound report and
plugging 7 subreports onto it...

the sql design element inside me shudders at this crude approach - but it
would be pretty easy and quick to do......sorry I can't offer anything more
elegant off the cuff...

 

[Guest]

That will do the trick, I forgot about the option to use subforms on an
ubound report.

Thanks