TOP N in a query

[CKEEV]

Hello,

Can someone please help me with a formula to show the Top 5 customers in given query broken out by Product Family? For Example: I want to see the top 5 customer sales for X product, the top 5 customers for Z product and so forth. I think I am having trouble grouping because it returns the top 5 customers overall, but not broken out by product family. Thanks for your help in advance. See the formula I am currently using below.

In (Select Top 5 [Pricing Quantity] From [Sales by Buying Group] Where [Product Family]=[Sales by Buying Group].[Product Family] Order By [Pricing Quantity] Desc)

 

[Norie]

This worked for me where tblSales had 3 fields Product, ProductGroup and SalesValue.

SELECT tblSales.Product, tblSales.ProductGroup, tblSales.SalesValue

FROM tblSales

WHERE tblSales.SalesValue In
(

SELECT TOP 5 SalesValue
FROM tblSales AS A
WHERE A.ProductGroup = tblSales.[ProductGroup]
ORDER BY SalesValue DESC
)

ORDER BY tblSales.ProductGroup, tblSales.SalesValue DESC;

 

[CKEEV]

Thanks So much for your help! It worked great.