troubleshooting Question

MS Access  Query = Counting total number of yes of all boolean fields in a table

Avatar of Mike Rudolph
Mike RudolphFlag for United States of America asked on
Microsoft AccessSQL
22 Comments1 Solution177 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros