Have Geek Brain

Access 2002 - how to write records by N groups and vertices into the same query

I have data that looks like this:

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

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

0


a source to share


5 answers


You need to use a unique ID, otherwise if you have multiple products in the same group with the same PercentAch, you will receive all of those products ... i.e. more than the 5% you wanted. Let's assume we have a unique ID on the Product. SQL would 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
);
+3


a source


Here is your answer. Sorry, but it took me a while to figure this out. I knew I had seen this before:



ACC: How to Create Top N Values ​​for Bulk Query: http://support.microsoft.com/kb/153747

+1


a source


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, then Top will act on the original source data, not the grouped data.

0


a source


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

Do you need the top 5% of entries in each group, or just the top 5? If you just want to get the top 5 records in each group, remove the PERCENT keyword from your query.

0


a source


I've been trying to figure out the exact same problem for most of the day. The answer is to just add Product

to the subquery WHERE

.

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% PercentAch values ​​for each Group / Product combination.

0


a source






More articles:


All Articles