CODEWITHSUNDEEP
ms-access
Jay
Jay

Reputation:

Access 2002 - how to Group By and Top N records in same query

I have data that looks like the following:

Group     Product    PercentAch
Gr1       Pr1        55%
Gr1       Pr2        65%
Gr2       Pr1        45%
Gr2       Pr2        95%
Gr3       Pr1        15%
Gr3       Pr2        35%
.
.
.

So basically the data describe a set of distinct products that are assigned to different groups. I am trying to create a query that will retrieve for me the top N% of records based on PercentAch by product by group. The Access help file says to sort by PercentAch in order to use the TOP property, but then that causes groups and products to be intermingled. My other option is to create a separate query for each Group/Product combination, which seems redundant. Is there a way to do this using just one (or two) queries?

Upvotes: 0

Views: 26469

Answers (5)

Mark Plumpton
Mark Plumpton

Reputation: 557

You need to use a unique identifier otherwise if you have multiple products in the same group with the same PercentAch you will get all of those products ... ie more than the top 5% you wanted. Assume we have a unique ID on the Product. The SQL will be:

SELECT Group, ProductID, Product, PercentAch
FROM SalesPerformance
WHERE ProductID IN (
    SELECT TOP 5 PERCENT ProductID
    FROM SalesPerformance as S
    WHERE S.Group = SalesPerformance.Group
    ORDER BY PercentAch DESC
);

Upvotes: 3

Tommy O'Dell
Tommy O'Dell

Reputation: 7109

I've been trying to figure out the exact same problem for most of the day. The answer it turns out was just to add Product to the subquery's WHERE clause.

SELECT Group, Product, PercentAch
FROM SalesPerformance
WHERE PercentAch IN (
    SELECT TOP 5 PERCENT PercentAch 
    FROM SalesPerformance as S
    WHERE S.Group = SalesPerformance.Group and S.Product = SalesPerformance.Product
    ORDER BY PercentAch DESC
);

This gave me the Top 5 % of PercentAch values for each Group/Product combination.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180787

Jay, here it is:

SELECT Group, Product, PercentAch
FROM SalesPerformance
WHERE PercentAch IN (
    SELECT TOP 5 PERCENT PercentAch 
    FROM SalesPerformance as S
    WHERE S.Group = SalesPerformance.Group
    ORDER BY PercentAch DESC
);

Did you want the top 5 percent of records in each group, or just the top 5? If you just want the top 5 records in each group, remove the PERCENT keyword from the query.

Upvotes: 0

Robert Harvey
Robert Harvey

Reputation: 180787

Here is your answer. Sorry but it took me awhile to get my mind around it. I knew I had seen this before:

ACC: How to Create a Top N Values per Group Query: http://support.microsoft.com/kb/153747

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180787

You need two queries. The Top query needs the output from the grouping query. If you try to put Top in the same query as Group, Top will act on the original source data, not the grouped data.

Upvotes: 0

Related Questions