Counting 1s and zeros (1 Viewer)

[echorley]

In the table (a small sample of a test where 1 is correct and 0 is incorrect. The test question number is in the header row) below, how would I get a count of the 1's and zeros for each school, A B and C, for each question 1 to 6?


School 1 2 3 4 5 6
A 1 1 1 1 1 0
A 1 0 1 0 0 1
B 0 0 1 1 0 1
B 1 0 1 0 0 0
A 1 0 1 1 0 1
B 1 0 0 1 0 1
A 1 1 1 0 1 0
C 1 0 0 1 0 1
C 1 1 1 0 1 1
A 1 1 1 1 1 1

 

[ajetrumpet]

sorry, but this is not explained very well. there is too little information to give an answer

 

[MStef]

Hello echorley!
1) I agree with ajetrumpet. I think your table not normalized well.
2) I improvized something.
Look at "DemoCount1and0A2000" (attachment, zip).
Open Form1N and see.

 

[namliam]

it would depend on your exact requirements, but I am guessing you will need a group by query and some union's

Select school, 'Q1' as Question , [1], count(*) from yourtable group by school, 'Q1'
Union all
Select school, 'Q2' as Question , [2], count(*) from yourtable group by school, 'Q2'
Union all
etc...

This data actually has a design issue in it, I presume it is a report of some kind from some place?

 

[Chrisopia]

i think people are over complicating things? You just want to count all the 1's right?

Total the column, the divide it by how many there are

= Sum ([Column1]) / Count ([Column1])

the 0's are just the number of 1's subtracted from the total amount.

or for letters use the domain count:

=DCount ([Column1],"tblSchool",Column1 = 'A')

Hope this helps.