Top and Botton 2 stores by District

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisB - 30 Mar 2007 01:44 GMT

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!

Reply to this Message

hmadyson - 30 Mar 2007 02:06 GMT

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.

> 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'
[quoted text clipped - 15 lines]
>
> Thanks for your help!

Reply to this Message

ChrisB - 30 Mar 2007 02:14 GMT

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.

> 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
[quoted text clipped - 23 lines]
> >
> > Thanks for your help!

Reply to this Message

Allen Browne - 30 Mar 2007 03:10 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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.
[quoted text clipped - 32 lines]
>> >
>> > Thanks for your help!

Reply to this Message

ChrisB - 30 Mar 2007 19:54 GMT

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!!

> See:
> Subquery Basics: TOP n records per group
[quoted text clipped - 37 lines]
> >> >
> >> > Thanks for your help!

Reply to this Message

ChrisB - 30 Mar 2007 22:38 GMT

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

> See:
> Subquery Basics: TOP n records per group
[quoted text clipped - 37 lines]
> >> >
> >> > Thanks for your help!

Reply to this Message