Top 25 records per group

[JKnightly]

I have a query and a table that are linked and I want to show the top 25 records for each group, I have found a few different forum posts and Microsoft articles about this exact question but cannot seem to get anything working. Here are some specific details about my query and table. The query sums all of the sales for the year by customer number.



Query: [Sales by Customer No for Current Year]
Fields: [CustomerNo], [Sales]

Table: [AR_Customer]
Fields: [CustomerNo], [UDF_CC_DROPDOWN]

I am trying to show the top 25 customers based on the sales volume grouped on customer class (the UDF_CC_DROPDOWN field).

I am probably missing something basic, any help would be greatly appreciated.

- Jonathan
Access 2010

[June7]

So what specifically have you found and tried? Have you seen http://allenbrowne.com/subquery-01.html#TopN

The query does not have customer class grouping.

You want the top 25 customers for each class? Or just the top 25 volume records?

[JKnightly]

I want to see the 25 customers with the highest sales volume for each customer class. I have looked at http://allenbrowne.com/subquery-01.html#TopN and could not seem to get the syntax or something right. And I looked at a few different Microsoft articles which referenced much earlier versions of access but I wasn't able to get the suggestions mentioned there working either.

Maybe you can tell me what I am doing wrong, here is the SQL:

SELECT AR_Customer.UDF_CC_DROPDOWN, [Sales by Customer No for Current Year].Sales, AR_Customer.CustomerNo
FROM [Sales by Customer No for Current Year] INNER JOIN AR_Customer ON [Sales by Customer No for Current Year].CustomerNo = AR_Customer.CustomerNo
WHERE ((([Sales by Customer No for Current Year].Sales) In (Select TOP 25 Sales
FROM [Sales by Customer No for Current Year] AS Dupe
WHERE Dupe.CustomerNo = AR_Customer.CustomerNo
ORDER BY AR_Customer.UDF_CC_DROPDOWN DESC, Dupe.Sales, Dupe.CustomerNO)))
ORDER BY AR_Customer.UDF_CC_DROPDOWN DESC;

The query just sits there when I run it. It shows running query in the bottom right hand corner but nothing happens, no error messages or results.

[June7]

Provide sample raw data. Can be attached file or build a table in post. An example of desired output would be nice.

If you want to return top 25 customers for each class, seems to me the [Sales by Customer No for Current Year] query will have to group by class as well as customer.