CODEWITHSUNDEEP
ms-access
Wallace Sean
Wallace Sean

Reputation: 23

Access - Get distinct count of rows based on different column values

I have an access database with the following format:

ID  | Period  | Data
---------------------
13  | 1       | xxxxx
13  | 2       | xxxxx
25  | 1       | xxxxx
25  | 2       | xxxxx
25  | 3       | xxxxx
52  | 1       | xxxxx
52  | 3       | xxxxx
94  | 1       | xxxxx
94  | 2       | xxxxx
94  | 3       | xxxxx

The Period can be 1, 2, or 3. I need to get a count of the IDs that have a period of 1, 2, and 3, or periods 1 and 2, or 1 and 3 (they always have period 1). How can I run such a query - it can be 3 different queries to hit the different permutations. The results would be like the following:

Periods 1, 2, and 3: Count: 2 (for IDs 25 and 94, although I don't necessarily need the IDs returned, just the count)

Periods 1 and 2: Count: 1 (ID 13)

Periods 1 and 3: Count: 1 (ID 52)

Is this possible with Access? It's not quite distinct values, so this question and the linked article don't seem to provide enough help. I don't have the database yet which is why I can't test that query, but I want to know if it would be possible before creating the table or if I need to find another solution.

Upvotes: 2

Views: 740

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

The easiest way to create a transform query like this and save it

TRANSFORM Count(YourTable.ID) AS CountOfID
SELECT YourTable.ID
FROM YourTable
GROUP BY YourTable.ID
PIVOT YourTable.Period)

Which outputs 

ID  1 2 3
--  - - -
13  1 1 
25  1 1 1
52  1   1
94  1 1 1

You can then use that create another query Lke this

SELECT t.Groups, Count(t.ID) AS CountOfID
FROM (SELECT ID, 
     SWITCH(
               [1] = 1 and [2] =1 and [3] = 1, 'Periods 1, 2, and 3',
               [1] = 1 and [2] =1 , 'Periods 1 and 2',
               [1] = 1 and [3] =1 , 'Periods 1 and 3'
               )  as Groups

FROM TheSavedTransformQuery)  AS t
GROUP BY t.Groups;

Which outputs this

Groups              CountOfID
------------------- ---------
Periods 1 and 2        1
Periods 1 and 3        1
Periods 1, 2, and 3    2

If you want to do it as single SQL statement this will work as well

SELECT 
    t.Groups, 
    Count(t.ID) AS CountOfID
FROM (

    SELECT 
        Switch(
            FirstPeriod.Period=1 And [SecondPeriod.Period ]=2 And [ThirdPeriod.Period]=3,'Periods 1, 2, and 3',
            FirstPeriod.Period=1 And [SecondPeriod.Period]=2,'Periods 1 and 2',
            FirstPeriod.Period=1 And [ThirdPeriod.Period]=3,'Periods 1 and 3') AS Groups, 
            DistinctID.ID
    FROM (((SELECT DISTINCT ID
        FROM 
        YourTable )  AS DistinctID 

        LEFT JOIN (SELECT ID, PERIOD 
        FROM YourTable
        WHERE Period = 1)  AS FirstPeriod 

        ON DistinctID.ID = FirstPeriod.ID) 

        LEFT JOIN (SELECT ID, PERIOD 
        FROM YourTable  WHERE Period = 2)  AS SecondPeriod 
        ON DistinctID.ID = SecondPeriod.ID) 

        LEFT JOIN (SELECT ID, PERIOD 
        FROM YourTable
        WHERE Period = 3)  AS ThirdPeriod 
        ON DistinctID.ID = ThirdPeriod.ID

)AS t
GROUP BY 
    t.Groups

Upvotes: 1

Related Questions