Counting number of times "A" appears in a table column

[pauladeanda]

Hi,

On my main table, I have a column that records what students'
responses are to a question (e.g. A, B, C, D, etc...). I'd like to
have fields that shows me how many people answered "A" to question 1,
"A" to question 2, etc... all on the same document. I've tried using
the count function, but I'm not sure how to get it to count the number
of A's. Is this the best way to do that or is there a better way?

thank you in advance for any ideas!
Paula

 

[scubadiver]

Why not use a 'group totals' query

select letter, count(letter)
from table
group by letter

How are your tables related to each other?

 

[Irene]

u try to create a query;

SELECT TableName.question, TableName.answer, Count(TableName.answer) AS
CountOfanswer
FROM TableName
GROUP BY TableName.question, TableName.answer;

then u can get the answer. hope this is the answer you want.

 

[BruceM]

An alternative approach is to use a report's Sorting and Grouping to group
by the field containing the letter. You will need to add to the detail
section a control bound to a field. I will say it is StudentID. You can
make the control invisible. In the group header or footer you can place an
unbound text box with the Control Source expression:
=Count([StudentID])

 

[Guillermo_Lopez]

Hi,

 On my main table, I have a column that records what students'
responses are to a question (e.g. A, B, C, D, etc...).  I'd like to
have fields that shows me how many people answered "A" to question 1,
"A" to question 2, etc...  all on the same document.  I've tried using
the count function, but I'm not sure how to get it to count the number
of A's.  Is this the best way to do that or is there a better way?

thank you in advance for any ideas!
Paula

I'd use a Count ... Iif... method.

Select [Whatever], count(iif([Question1]="A",[Question1],Null)) as
AsOnQ1 From Questionaire Group By [Whatever].

Count only counts non-null values.

I hope this helps.

- GL

 

[pauladeanda]

Hi all,

Sorry for taking so long to reply to your suggestions- thank you for
all the great ideas! I tried a couple of the responses and both seemed
to work- only one shows me how many people responded to each of the
answers (A, B, C, D, etc...) the other one (the iif statement) shows
all the letters, but only has a number next to the response that I was
initially interested in and 0 for all of the other responses. Both
will be useful for different questions that I have about my data- so
thank you very much!

Now, I have a follow up question. I would like to have my query show
the responses for all of the questions that way I don't have to open
up 25 different queries to get a sense of how many students got each
question right. However, when I add more columns to show question 2 it
still ultimately shows me how many A's were selected for the first
question, but it does not put all the A's together as the table below
shows. Instead, it has three categories for A, 2 for D, etc... (the
numbers do add up correctly though). I'm not sure why it is doing
that. Any ideas?? I've included the SQL code below the table.

SA1 CountOfA SA2 CountOfF
10 10
A 8 F 8
A 1 H 1
A 1 J 1
C 1 F 1
D 1 F 1
D 2 G 2
E 1 F 1

Here is the SQL code that is producing this table.

SELECT Table1.SA1, Count(Table1.A) AS CountOfA, Table1.[SA2],
Count(Table1.F) AS CountOfF
FROM Table1
GROUP BY Table1.SA1, Table1.A, Table1.SA2, Table1.F;

much appreciative,
Paula

 

[Ruben]

Hi all,

Sorry for taking so long to reply to your suggestions- thank you for
all the great ideas! I tried a couple of the responses and both seemed
to work- only one shows me how many people responded to each of the
answers (A, B, C, D, etc...) the other one (the iif statement) shows
all the letters, but only has a number next to the response that I was
initially interested in and 0 for all of the other responses. Both
will be useful for different questions that I have about my data- so
thank you very much!

Now, I have a follow up question. I would like to have my query show
the responses for all of the questions that way I don't have to open
up 25 different queries to get a sense of how many students got each
question right. However, when I add more columns to show question 2 it
still ultimately shows me how many A's were selected for the first
question, but it does not put all the A's together as the table below
shows. Instead, it has three categories for A, 2 for D, etc... (the
numbers do add up correctly though). I'm not sure why it is doing
that. Any ideas?? I've included the SQL code below the table.

SA1 CountOfA SA2 CountOfF
10 10
A 8 F 8
A 1 H 1
A 1 J 1
C 1 F 1
D 1 F 1
D 2 G 2
E 1 F 1

Here is the SQL code that is producing this table.

SELECT Table1.SA1, Count(Table1.A) AS CountOfA, Table1.[SA2],
Count(Table1.F) AS CountOfF
FROM Table1
GROUP BY Table1.SA1, Table1.A, Table1.SA2, Table1.F;

much appreciative,
Paula

 

[Guillermo_Lopez]

Hi all,

Sorry for taking so long to reply to your suggestions- thank you for
all the great ideas! I tried a couple of the responses and both seemed
to work- only one shows me how many people responded to each of the
answers (A, B, C, D, etc...) the other one (the iif statement) shows
all the letters, but only has a number next to the response that I was
initially interested in and 0 for all of the other responses.  Both
will be useful for different questions that I have about my data- so
thank you very much!

 Now, I have a follow up question.  I would like to have my query show
the responses for all of the questions that way I don't have to open
up 25 different queries to get a sense of how many students got each
question right. However, when I add more columns to show question 2 it
still ultimately shows me how many A's were selected for the first
question, but it does not put all the A's together as the table below
shows.  Instead, it has three categories for A, 2 for D, etc... (the
numbers do add up correctly though).  I'm not sure why it is doing
that.  Any ideas??  I've included the SQL code below the table.

SA1     CountOfA        SA2     CountOfF
                10                             10
A               8                 F             8
A               1                 H             1
A               1                 J              1
C               1                 F             1
D               1                 F             1
D               2                 G             2
E               1                 F             1

Here is the SQL code that is producing this table.

SELECT Table1.SA1, Count(Table1.A) AS CountOfA, Table1.[SA2],
Count(Table1.F) AS CountOfF
FROM Table1
GROUP BY Table1.SA1, Table1.A, Table1.SA2, Table1.F;

much appreciative,
Paula

I'm having trouble understanding what you really need. I'm confused as
to the meaning of SA1, SA2, Table1.A, Table1.F. I'll try to guess what
you need. Hopefully it will give you ideas to mess around with what
you really need.

You have grouping issues.

If you group on all questions you are going to group the counts as
"How many students answered Q1 = A, Q2 = C, Q3 = F, ..." And so forth
for every posible solution. If you want to have one question per
Column, then you need One query for each question and group by
Answers. However this means having 21 queries. However you can
transpose it. Group by Questions and count how many As, Bs, Cs, Ds and
Fs there are.

With the count... Iif you can have one Leter per column, so just one
Query. you can also do 5 queries.

SELECT Table1.Question1, Count(Table1.A) as CountOfA
FROM Table1
WHERE Table1.SA1 = "A"
Group By Table1.Question1

You do this with all 5 answers.

-GL