Top 25 query problem

[tmcrouse]

I have a table that has all the state 2 letter codes. The market mapping which will take the state two letter code and map the market. Such as AL will have multiple markets. AZ multiple markets. Then it has paper volumes for the year 2008.

I know how to get the top 25 paper volumes in descending order. But what I need is how to get the top 25 paper volumes in descending order per market mapping. When I run my non top 25 query, I get 14k rows back. I need to get the top 25 per market mapping and right now am doing it manually. I ascend my market mapping and descend my paper volumes. So right now it might look like this:

Market Mapping Region TaxID Paper Volume
Alabama Southeast 000000 200000
Alabama Southeast 111111 199900
Alabama Southeast 222222 190000
and so on. Alabama market mapping returns 2700 rows. I just want to take the top 25 of that market mapping. After the Alabama market mapping then it is Arizona. Now I can't simply do this by state two letter because the states have multiple market mappings. We do everything here by market mapping. So say for instance I have PA. Well PA is broken down into the market mapping of


E. Pennsylvania
W. Pennsylvania.
So I need the top 25 for each of the market mappings based on the top 25 highest paper volumes. I use MS Access and trying to figure out what exactly to put in my criteria to get my results.

Thanks!!!

[tmcrouse]

Well I see I have had multiple views with not 1 reply. Guess this is a question no one can answer. I am sure there is a way to do it but no one seems to know how. So I guess I have to continue the way I am doing it which takes forever. At present I have to do top 25 in Access to get top 25 highest values and then in criteria for the state put in AL or if AZ, put in AZ. I have to do this 51 times. When doing AL I copy and paste to Excel. Then go back and do AZ copy and paste that below AL in Excel, etc etc until I have 1275 total rows. All the top 25 for each state. It is just a very slow and kind of dumb process to me. I would think I could just have a query that says give me top 25 per state. But can't seem to find how exactly to do this in Access.

[thhui]

Use a for loop to
create a union query so that
it will loop 52 times to
create a long query string first.

Then run the query in vba and get the result.

[tmcrouse]

I was able to figure it out by putting in the criteria section

in(select...............and a sort of lengthy bit of information)

Doing it this way I didn't have to create a VBA anything since I am not very up on VB. Has been awhile since college and doing it as a job so I don't remember it.