I have a table with studentID, Quarter #, then several boolean fields - roughly 15. I need to figure out how to count ALL the "yes" and all the "no" values for all students for a particular Quarter in the table. So I know the query will filter by Quarter 1 through Quarter 4 but then somehow I need to start with each student row and add up all the "yes" values for each field then add that to all the "yes" values for the next student and so on. Here is what the table structure looks like:

[ID] PK

[stdID] - Short Text

[Qtr] - Number

[field 1] - Yes/No

[field 2] - Yes/No

[field 3] - Yes/No

[field 4] - Yes/No

[field 5] - Yes/No

[field 6] - Yes/No

[field 7] - Yes/No

[field 8] - Yes/No

[field 9] - Yes/No

[field 10] - Yes/No

[field 11] - Yes/No

[field 12] - Yes/No

[field 13] - Yes/No

[field 14] - Yes/No

[field 15] - Yes/No

So basically for each student I need to count how many fields have a "yes" and how many fields have a "no" then add that to the next overall count and so on. Ultimately the query needs to tell me that I have a total of so many yes's and a total of so many no's then somewhere I need to express the percentage of yes's to no's. That way the teacher can know how many total students said yes and how may said no. These fields are basically questions and the teacher is going to click [yes] if the student got it right and leave blank if they got it wrong and I want to show a percentage of correct answers opposed to incorrect answer for the whole class.

Hope this makes sense and any assistance would be most appreciated!

Thanks!

No is stored as 0 but for a "blank" we can substitute a zero using NZ()

For some reason -1 is used for Yes in Access so to ignore the sign, use ABS()

i.e.

ABS( NZ( table.field, 0 ) )

To add the columns just use a plus symbol between each, so in SQL it will look something like this

SELECT

Sum(

abs(nz(Table1.q1,0)

+abs(nz(Table1.q2,0))

+abs(nz(Table1.q3,0))

)) AS SumOfAnswers

FROM Table1;

one could just use ABS() once e.g.

SELECT

SUM(

ABS(

nz(Table1.q1,0)

+nz(Table1.q2,0)

+nz(Table1.q3,0)

)

) AS SumOfAnswers

FROM Table1;