Mysql – How to Query Entries with Specific Decision IDs in MS Access

I have a table named MyTable linked from an external MS Access database (C:DataLinkedDatabasesExternalData.mdb). The table structure includes GroupId, RuleId, and DecisionCode.

I need to create a query that lists all entries from this table where the values 10 and 42 both exist in the DecisionCode field under the same GroupId and RuleId. However, it should only return these entries if both DecisionCode values 10 and 42 exist for the same GroupId and RuleId.

Here is the query I initially wrote, which results in a syntax error:

SELECT A.*
FROM (SELECT * FROM MyTable IN 'C:DataLinkedDatabasesExternalData.mdb') AS A
WHERE EXISTS (
SELECT 1
FROM (
    SELECT GroupId, RuleId
    FROM MyTable IN 'C:DataLinkedDatabasesExternalData.mdb'
    WHERE DecisionCode IN (10, 42)
    GROUP BY GroupId, RuleId
    HAVING COUNT(DISTINCT DecisionCode) = 2
) AS B
WHERE A.GroupId = B.GroupId
AND A.RuleId = B.RuleId
)
AND A.DecisionCode IN (10, 42);

I encountered a syntax error: "missing operator in query expression ‘COUNT(DISTINCT DecisionCode) = 2’".

I understand that DISTINCT is not supported in the COUNT function in MS Access. How can I rewrite this query to achieve the desired result?

Here’s the revised attempt, which still doesn’t return the correct results:

SELECT A.*
FROM (SELECT * FROM MyTable IN 'C:DataLinkedDatabasesExternalData.mdb') AS A
WHERE EXISTS (
    SELECT 1
    FROM (
        SELECT GroupId, RuleId
        FROM MyTable IN 'C:DataLinkedDatabasesExternalData.mdb'
        WHERE DecisionCode IN (10, 42)
        GROUP BY GroupId, RuleId
        HAVING COUNT(*) = 2
    ) AS B
    WHERE A.GroupId = B.GroupId
    AND A.RuleId = B.RuleId
    AND EXISTS (
        SELECT 1
        FROM MyTable AS C
        WHERE C.GroupId = A.GroupId
        AND C.RuleId = A.RuleId
        AND C.DecisionCode = 10
    )
    AND EXISTS (
        SELECT 1
        FROM MyTable AS D
        WHERE D.GroupId = A.GroupId
        AND D.RuleId = A.RuleId
        AND D.DecisionCode = 42
    )
)
AND A.DecisionCode IN (10, 42);

This query includes nested subqueries to ensure that both DecisionCode 10 and 42 exist for the same GroupId and RuleId. However, it still returns entries that only contain DecisionCode 10.

Any advice on how to correctly structure this query in MS Access?

Example Data:

Desired outcome: