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

ms-access

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?

Best Answer

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
);
Related Topic