Counting A's in a table column 1, F's in column 2, etc...

[pauladeanda]

Hi,

I have a database where you can type in students' responses to
questions on a test.
I was able to create a count on a query that shows me how many
people selected the answer A (the correct answer) to question 1, F to
question 2, etc... 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.

When I just do a query with a count for question 1, the function works
accordingly telling me I have 10 A's, 2 C's, etc... 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

 

[Wayne-I-M]

Not sure what you're want to do so use this (then delete the bit your don't
want).

SELECT Count(IIf([TableName]![SA1]="A",1)) AS CountOfA,
Count(IIf([TableName]![SA1]="A" Or [TableName]![SA2]="A" Or
[TableName]![SA3]="A" Or [TableName]![SA4]="A" Or [TableName]![SA5]="A" Or
[TableName]![SA6]="A",1)) AS FullCountOfA
FROM TableName;


Do the same for B and C and D, etc, etc

 

[John W. Vinson]

SELECT Count(IIf([TableName]![SA1]="A",1)) AS CountOfA,
Count(IIf([TableName]![SA1]="A" Or [TableName]![SA2]="A" Or
[TableName]![SA3]="A" Or [TableName]![SA4]="A" Or [TableName]![SA5]="A" Or
[TableName]![SA6]="A",1)) AS FullCountOfA
FROM TableName;

Wayne, that's close... but won't give the desired result. Count() will return
a count of all records whether the field contains "A" or not. If you're going
to do this, use

Sum(IIF([TableName].[SA1] = "A", 1, 0))

Summing the 1's and 0's will give the desired count.

There are probably other ways to get the OP's desired counts but nothing
springs instantly to mind (other than that the table should be normalized).

 

[pauladeanda]

Great! Actually, both of your suggestions produced the same result
for summing up the number of A's for question 1 (without including the
FullcountofA piece in the first suggestion). The results now show me
how many people put the other answers anymore, but ultimately that
wasn't as important to me as getting a clean representation of the
total number of A's. Thanks so much for both of your suggestions.
Below is the text for the SQL code that I ultimately used; it is
working great!

SELECT Count(IIf([Table1]![SA1]="A",1)) AS CountOf1A,
Count(IIf([Table1]![SA2]="F", 1)) AS Countof2F, Count(IIf([Table1]!
[SA3]="C", 1)) AS Countof3C, Count(IIf([Table1]![SA4]="J", 1)) AS
Countof4J, Count(IIf([Table1]![SA5]="B", 1)) AS Countof5B,
Count(IIf([Table1]![SA6]="G", 1)) AS Countof6G, Count(IIf([Table1]!
[SA7]="A", 1)) AS Countof7A, Count(IIf([Table1]![SA8]="G", 1)) AS
Countof8G, Count(IIf([Table1]![SA9]="D", 1)) AS Countof9D,
Count(IIf([Table1]![SA10]="J", 1)) AS Countof10J, Count(IIf([Table1]!
[SA11]="B", 1)) AS Countof11B, Count(IIf([Table1]![SA12]="H", 1)) AS
Countof12H,
FROM Table1;

The table that results looks like the following:

CountOf1A Countof2F Countof3C Countof4J Countof5B Countof6G Countof7A
Countof8G
10 11 6 8
7 10 7 3

much appreciative,
Paula