MS Access 2010 SQL Top N query by group performanc

Question

I have 2 tables, one with a measure of items (MASE_Table) and one with item attributes (SKU). The item attributes can be the Planner or ABCByPick or XYZ or other attributes.

With a query I want to get the top 10 items of this measure (AvgOfScaledError) by the attribute ABCByPick.

I would like to make the query so generic as possible so that I can copy it and replace the ABCByPick with the attribute planner

I have found tips in here which I've tried. As Long as the number of records in the MASE_Table are less 1000 I can exectute the query. If I want to run it with 8000 records in MASE_Table the query is not anymore producing a result (no error message but time-out).

Here is my query for the top 10 of ABCByPick:

SELECT s2.ABCByPick
       ,m2.DmdUnit
       ,ROUND(m2.AvgOfScaledError, 2) AS MASE
    FROM MASE_Table AS m2
    INNER JOIN SKU AS s2
        ON ( m2.Loc = s2.Loc )
           AND ( m2.DmdUnit = s2.Item )
    WHERE m2.AvgOfScaledError IN ( SELECT TOP 10 m1.AvgOfScaledError
                                    FROM (
                                           SELECT m1.Loc
                                               ,m1.DmdUnit
                                               ,s1.ABCByPick
                                               ,m1.AvgOfScaledError
                                            FROM MASE_Table AS m1
                                            INNER JOIN SKU AS s1
                                                ON m1.Loc = s1.Loc
                                                   AND m1.DmdUnit = s1.Item
                                         )
                                    WHERE s1.ABCByPick = s2.ABCByPick
                                    ORDER BY m1.AvgOfScaledError DESC )
    ORDER BY s2.ABCByPick
       ,m2.AvgOfScaledError DESC;

Primary keys on MASE_Table are Loc and DmdUnit
Primary keys on SKU are Loc and Item

Answer 1

This should help some:

SELECT s2.ABCByPick,
    m2.DmdUnit,
    ROUND(m2.AvgOfScaledError, 2) AS MASE
FROM MASE_Table AS m2
INNER JOIN SKU AS s2 ON m2.Loc = s2.Loc
    AND m2.DmdUnit = s2.Item
WHERE m2.AvgOfScaledError IN (
        SELECT TOP 10 m1.AvgOfScaledError
        FROM MASE_Table AS m1
        INNER JOIN SKU AS s1 ON m1.Loc = s1.Loc
            AND m1.DmdUnit = s1.Item
        WHERE s1.ABCByPick = s2.ABCByPick
        ORDER BY m1.AvgOfScaledError DESC
        )
ORDER BY s2.ABCByPick,
    m2.AvgOfScaledError DESC;