CODEWITHSUNDEEP
countms-access-2013
Chris
Chris

Reputation: 353

Does MS Access have a COUNTIF function?

I'm administering a survey that uses a special scale called Best-Worst scaling.

After the survey is closed, I end up with a spreadsheet that looks like this: 

                B1(1) B1(2) B2(1) B2(2)
participant 1     4     5    7      8 
participant 2     2     11   7     13 
participant 3     5     4    7     13

This means that in block 1, participant 1 selected item # 4 as the most important, and item #5 as the least important, etc.

I would like to create a query that shows me the number of times each item was ranked "most important" and "least important" in each block. I want to create a query that looks like this:

            B1(1)   B1(2)   B2(1)   B2(2)

item 1      5        1          1       3
item 2      1        1          6       7
item 3      4        5          3       2
...         ...     ...         ...     ...
item 13     2        5          0       7

This means that item #1 was selected as the most important by 5 people in block 1, and 1 time in block 2 etc...

In Excel I can do this using the COUNTIF function: 

   COUNTIF(COLUMN RANGE,"="ITEM NUMBER)

I'd like to know if I can reproduce this in MS Access and how.

Upvotes: 1

Views: 4098

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123584

For data in a table named [SurveyDataRaw]

participant    B1_1  B1_2  B2_1  B2_2
-------------  ----  ----  ----  ----
participant 1     4     5     7     8
participant 2     2    11     7    13
participant 3     5     4     7    13

you need to start by creating a saved query in Access named [SurveyDataUnpivoted]

    SELECT participant, "B1_1" AS blockRank, B1_1 AS item
    FROM SurveyDataRaw
UNION ALL
    SELECT participant, "B1_2" AS blockRank, B1_2 AS item
    FROM SurveyDataRaw
UNION ALL
    SELECT participant, "B2_1" AS blockRank, B2_1 AS item
    FROM SurveyDataRaw
UNION ALL
    SELECT participant, "B2_2" AS blockRank, B2_2 AS item
    FROM SurveyDataRaw

That query returns

participant    blockRank  item
-------------  ---------  ----
participant 1  B1_1          4
participant 2  B1_1          2
participant 3  B1_1          5
participant 1  B1_2          5
participant 2  B1_2         11
participant 3  B1_2          4
participant 1  B2_1          7
participant 2  B2_1          7
participant 3  B2_1          7
participant 1  B2_2          8
participant 2  B2_2         13
participant 3  B2_2         13

Now you can create a crosstab query to summarize the above data by [item]

TRANSFORM IIf(IsNull(Count(participant)), 0, Count(participant)) AS CountOfparticipant
SELECT item
FROM SurveyDataUnpivoted
GROUP BY item
PIVOT blockRank

producing

item  B1_1  B1_2  B2_1  B2_2
----  ----  ----  ----  ----
   2     1     0     0     0
   4     1     1     0     0
   5     1     1     0     0
   7     0     0     3     0
   8     0     0     0     1
  11     0     1     0     0
  13     0     0     0     2

Upvotes: 1

Related Questions