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

Question

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?

Answer 1

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.

Answer 2

Hi, Jay.

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

Answer 3

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.

Answer 4

Robert - thank you. this change plus one other change produced exactly the results I was looking for. I bow before your superior access knowledge. Your patience is also much appreciated!

Answer 5

Hi Robert, are you aware regarding the plus change made by Jay? The query above does not work for each groups :-(