Top and Botton 2 stores by District

[Guest]

I have a MS Access table with 3 columns, District, Store, and Rank. Within
the table, there are 300 Stores that are members of 29 Districts. The 'Rank'
column ranks each store by total sales from 1 to 300 regardless of what
district they belong to. I need to create and report on the top and bottom 2
stores by each district.

For Example (One District Only):

District Store Rank
Atlanta 2862 1
Atlanta 2864 14
Atlanta 2877 27
Atlanta 1666 32
Atlanta 7943 37
Atlanta 6254 48

I need the query to show the top and bottom two stores.

Thanks for your help!

 

[Guest]

Create a query and put the fields that you want in order that you want them
ranked. So you will need one sorted for the top values on top, and one sorted
for the bottom values on top. Right click on the gray of the query designed.
You will see a field called Top, there you will list the number of values
that you want to show (2), and it will be limited.

Let me know if this was helpful and if I can provide any more assistance.

 

[Guest]

I did this and it returned the top 2 for Atlanta, but I also need the top 2
districts for the remaining 28 other districts in the table.

 

[Allen Browne]

See:
Subquery Basics: TOP n records per group
at:
http://allenbrowne.com/subquery-01.html#TopN

 

[Guest]

Thanks Allen. I know that I must be doing something wrong because it's not
quite getting the desired results. Here's the code that MS Access produces.
It adds HAVING and parentheses.

SELECT qry_rank_supply.District, qry_rank_supply.Rank
FROM qry_rank_supply
GROUP BY qry_rank_supply.District, qry_rank_supply.Rank
HAVING (((qry_rank_supply.Rank) In (SELECT TOP 2 Rank
FROM qry_rank_supply
WHERE qry_rank_supply.District = tbl_Store_Data.District)))
ORDER BY qry_rank_supply.District;

The code above gives me the top two of just the first district.

Thanks again for the help!!

 

[Guest]

Thanks to everyone on this string for helping me. It works great.