Top 25 per state

[tmcrouse]

I have a database in Access and a table that contains about 190k rows. In this table I have all 51 states and each state has claim numbers. These claim numbers range from 1 claim for the year 2008 all the way to 1 million. I want to run a query that will pull my top 25 largest claim volumes per state. I can descend my claim volume row to get the largest at the top and then ascend my state codes and it will show the greatest amount first with the state code. Such as I have AL first massive rows with AL first and the first row has claim volume 6099, 2nd AL row is 5335 etc etc. Then it changes to AZ and has the greatest there and so on. I want the top 25 largest per state and have been trying to figure out how to run a query to get that. Any help would be appreciated. I don't want to do it as I am now with top 25 and put in the state of AL and there I have all AL. Then do the same for AZ and as I am doing this I have to copy and paste each one into a spreadsheet so only the top 25 per state are provided. This is time consuming and if there is 1 query I can do to get all the top 25 per state in one swoop that would be fantastic.

[Allan Murphy]

Use Help and look up TOP

ALL, DISTINCT, DISTINCTROW, TOP Predicates

The TOP Predicate returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

[GolferGuy]

I assume that you want this query so you can feed a report. at which point all you need to do is run a query of the entire table, then within the report you can skip all records after the top 25 for each state. If you are looking for something other than a report, I would suggest building a temporary work table in your database, then run 51 append queries from VBA code. Each query will be exactly like all the rest, except for the State code. Your VBA code can loop through a table that has your 51 state codes in it to control the running of the query 51 times. Of course this query will be getting the top 25 for each state code. By adding these 25 records into the work table, you can then display those records on a form, or in a query of just those 1275 (25 * 51) records.

[tmcrouse]

Well this isn't going to work because I am not running 51 append queries. The goal is to run 1 query that will include all states top 25. At present the report is done by not including any top values and just running query. It returns 11k records total. Then I descend by the 2008PaperVolume and choose the top 25 in that state and delete the rest for say AL from the table. Then I have AZ next and choose top 25 and delete the rest and so on until I get to the end which is WY. This is a very manual process and redundant. I want a query that I can do top 25 per state. I tried putting in the State column where Criteria is Alabama, Arizona etc and everytime it shows nothing. But if I just put in AL that is returned top 25. Then put in AZ that is returned top 25.etc. This just seems so archaic. There has to be a way in Access to accomplish this task. If there isn't then I find the programmers of Access to not be on top of the programming after multiple years of Access being in existence. This is a basic function that I find many organizations utilize and to not include this in the programming to me means the PM over at MS is lacking in their PM and programming knowledge. And I am now going to pose this question to my inlaws that work for MS. 1 of which is a mgr on the Access help desk. I just refuse to believe there isn't a way to accomplish this task with 1 query.

[tmcrouse]

I had to create a separate table that just housed my states. So I created market and then market mapping.

Then the table that has the over 11k records in it was left joined using the market mapping within that table.

Then my first item I choose for query was market mapping from market table and choose distinct records. Then from the 2nd table that is called exantepaper2 all the fields are placed within the query. The market mapping in exantepaper2 has this in the criteria:
in(select top 25[exantepaper2].[market mapping from [exantepaper2] where [market].[market mapping] = [exantepaper2].[market mapping] order by [2008paper] desc)

This gives me the top 25 in each market and sorts by descending for the paper volume cuz I want the top paper submitters. I also did ascending for the market mapping so that my final gives me alabama followed by arizona, etc.

I will post this everywhere I asked this question for others that might come across this.