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

You need to treat each "blank" or No as zero, and each Yes as 1

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;

Paul gave you a partial solution.  He showed you how to sum the yes answers.  If he checks back in and gives you the complete solution, give him the points.  Otherwise, I'll take a stab at it tomorrow.

What Paul didn't mention was that your design is poor and will cause nothing but trouble going forward.  Essentially, you have taken a spreadsheet and called it a table.  Already you are wondering why there is no function that can do this simple calculation for you.  This is a snap with Excel.  The answer is that relational databases are not spreadsheets.  They do NOT support non-normalized design patterns such as functions that work across multiple columns.  Each of the questions should be stored as a separate row in a new table.  then the query would be:

Select StdID, Qtr, Abs(Sum(SomeYNField)) As YesAnswers, Count(*) - Abs(Sum(SomeYNField)) As NoAnswers, Abs(Sum(SomeYNField)) / Count(*) As YesPct
From YourTable
Group By StdID, Qtr;

Notice that the above query works REGARDLESS of how many Y/N instances (rows) exist in the table.  Whether there are 15 or 499 or 3,487,801, the calculations do not change.  The query is identical.  So, if you find next month that you need 16 Y/N fields, you won't need to change anything!  Try that with your spreadsheet like design.

PS - StdID should almost certainly be an autonumber and so a long integer.  People expect certain things like when you call something an "ID" it is an autonumber.  If you call it a Date, it is a date, etc.  You can of  course name things any way you want as long as Access doesn't complain but over time, you will come to understand that standards that mean something will make your life a whole lot easier.

As the No values are Null, all you need is to check for Null:

Select
    [Qtr],
    Sum(Abs(
        ([field 1] Is Not Null) + 
        ([field 2] Is Not Null) + 
        ([field 3] Is Not Null) + 
        ([field 4] Is Not Null) + 
        ([field 5] Is Not Null) + 
        ([field 6] Is Not Null) + 
        ([field 7] Is Not Null) + 
        ([field 8] Is Not Null) + 
        ([field 9] Is Not Null) + 
        ([field 10] Is Not Null) + 
        ([field 11] Is Not Null) + 
        ([field 12] Is Not Null) + 
        ([field 13] Is Not Null) + 
        ([field 14] Is Not Null) + 
        ([field 15] Is Not Null))) / Count(*) / 15 As YesPercentage
From
    YourTable
Group By
    [Qtr]

Select all Open in new window

That said, follow Pat's advise and normalise your tables, indeed if the count of Yes/No is "roughly" 15 only.

Each of the questions should be stored as a separate row

absolutely agree! (ran short of time earlier)

Looks like an excel sheet.
Why not doing it in excel?
You may link the excel sheet to access and do more analysis if needed.

Gus,
I don't think that syntax is correct.

BTW, The syntax I suggested for a normalized schema works even if some values are null.  Although, If Yes and No are acrually the only valid values, the field should be defined with a default of 0 or -1 depending on whether you want the default to be No or Yes.  This is actually critical with an un-normalized structure as you have currently because you will be constantly fighting with nulls in these fields.

The "correct" syntax would be:

Sum(Abs(
        (Nz([field 1], 0) +
        Nz([field 2], 0) +
        Nz([field 3], 0) +
    .....

Avoid Null issue and using Nz. In table design set default values of fields to 0.

Why not upload a sample database and an image of expected output. Include few representative records.

Thank Guys for the input. I agree that the tables need to be normalized better but I've really struggled on how do this. There is an exact number of "High Frequency Words", and exact number of "shapes" to identify...etc. I have attached the Application for your review. Please let me know what you think. Don't hold back  :-)  I do have a timeline on this so If you belief a quick change and can help normalize...great! Each quarter will have identical values. currently each tab (quarter) is filtered by quarter. I used toggle button so select values understood by the student.

I have completed all sub forms for Pre-Qtr...I just need to create sub forms for remaining Quarters. But if you can think of a better way I'm all ears. Thank you!

From the home page if you select Ctrl+~ (tilde) you can view the actual tables.
KindergartenReportCard_NEW.accdb

Avoid Null issue and using Nz

As I said, define a default and make a value required if it makes sense.  I have a number of situations where "unknown" has meaning so I can't define the default to be Yes or No.

Shogun5,
For starters - tblHFW, tblShapes, tblBlending, tblRhyming, tblLtrRecognition should all be a single table.  All the columns (except the PK and FK) in those tables are "data" and belong as rows in a single table rather than columns in 5 tables.  That would have simplified a lot.  Plus, it would make the evaluation infinitely expandable.  You have another table that lists all the items and groups them as well.  Then the data table has just a couple of columns
SkillEvaluationID (autonumber PK)
SkillID (FK to table that lists all possible skills and their groups)
SkillEvalResultYN

Simplify access to the database, removing splash and disabling Autoexec macro.
Point to the specific objects that demonstrate the issue.
 
Lots of tables.
I assume the count for tblQuestions.
Some records has a null StudentID, is each another entry for a student in previous record?

You used the question text as a field name. If I have to ignore normalization for a near future, I  may use q1, q2, q3 ….

Look at tables h_Questions_codes and h_tblQuestions and query h_questions_query. Hope that gives a hint to improve the design.
KindergartenReportCard_NEW-2.accdb

Pat and Hnasr...okay...this is starting to make sense. Wow I was really off and making more work for myself. Hnasr thanks the adding the tables and query to the database. Wonder why the query has two empty rows?

Okay...I'm going to redo the tables and show you guys for input...

Okay...I have stripped the tables and can make one table with question numbers and one table with codes as hnasr suggests. Please see attached images. How do I filter by quarter then by category. I need to be able to add how many correct answers per student per category.

Thank you in advance for your assistance@!
Kindergarten-Assessment-App.png
Kindergarten-Assessment-App2.png

Nope, that's not it either.  You need 1 table to define the questions and a separate table to log the results.  Both have a single row for each question.  The whole point of the structure is to allow you to add new questions whenever you need to.  I doubt that the schema will change dramatically but it could change over time.  This structure also allows for much easier analysis.  There are two ways to handle adding rows to tblAnawers.  You can run an append query that copies all the rows in tblQuestions and adds them to tblAnswers (you will need to include the correct StudentID for this to work).  In this case, the Answer must be added as null rather than Yes or No since the answer is unknown at that time and it isn't until you update each and every question that the answer will be known.  You can create queries that count the null values so you can keep track of what is still missing.  The second option is to add the rows one at a time as the answers are acquired.

tblQuestions
QuestionID (autonumber PK)
GroupID (FK to tblGroups so you can group the questions like your original 5 tables if that makes sense to you)
QuestionSeq (number to sequence the questions within the group.  You need to manage this so you can move questions around. Number by 100's instead of by 1's so it is easy to move questions around)
QuestionText

tblAnswers
AnswerID (autonumber PK)
StudentID (FK to tblStudent)
Qtr
Answer

You also need to move the comments out of the student table into a second table.  This structure allows multiple comments per quarter.  
tblComments
CommentID (PK autonumber)
StudentID (FK to tblStudent)
Qtr
Comments

Does this look better?  Questions I have:

1. How to I connect the Answer to the question? Your suggestions students related to answers but there is no relationship to questions. Also the Qtr field is in two tables and not sure this is normalized as there will be some redundancies. There is a one to many relationship between groups and questions which is good but I'll need to figure out the following:

a. How to filter by qtr then group to aggregated all the boolean correct answers for a given group.

If I am way off here then perhaps you could create a sample database showing the relationship from student. Sorry...but I am trying to learn the best normalized way here and I do appreicate everyone's help.

Wonder why the query has two empty rows?

Table h_tblQuestions has 2 records with null ID, and that appears in query h_questions_query

Table: h_tblQuestions

ID	StdID	       Qtr	q1	q2
1		        0	Yes	No
2	5635626345	0	No	Yes
3	9999999999	0	Yes	Yes
4		        0	Yes	Yes
5	9999999995	0	Yes	Yes

Select all Open in new window

Query: h_questions_query

ID	StdID	Qtr	yes Count
1		        0	5
2	5635626345	0	3
3	9999999999	0	4
4		        0	3
5	9999999995	0	3

Select all Open in new window

tblQuestions should NOT contain StdID.  tblQuestions only contains the raw question text.  tblAnswers contains the answers given by the StdID.

You need to post the SQL string if you want us to help figure out what is wrong with the query.  I'm going to guess that 5 of the rows have null values in StdID and 3 of the rows have ZLS values.  You can't tell the difference between Null and ZLS just by looking.  ZLS is what you get when you set something = "" and Null is what you get when you set something = null.

One common way to get ZLS in a text field (Zero Length STRING) is by editing the record with a form and instead of using select and the del key to remove characters, the user enters a space or just backspaces over the characters.

tblQuestions should NOT contain StdID

I mentioned before "If I have to ignore normalization for a near future, I  may use q1, q2, q3 …."

Without sufficient explanation of the issue, each contributor assumes things to provide help.
 
I assumed tblQuestions contains answers to few questions.

To get proper help, which I suggest, needs to be done in a piecewise process.

One other way to tackle this is to use a bit of binary representation..
Instead of having 15 fields or even more you could have a single text field ..eg. Choices ( i haven't checked thoroughly the whole discussion )
and then map the check boxes to one char of the  text string
Something like
checkBox1 = mid(Choices,1,1)
.............
checkBox10 = mid(Choices,10,1)
....
so eventually you could have something like
100100010001011
and do a
len(replace("100100010001011","0","")) to get the number if YES
I have used it in the past with a situation needed a lot of checkboxes and worked just fine...i even went to some event sinking and put all the update code to a single module...but for start you can just use the AfterUpdate event of each of the checkboxes to manipulate the "choices" string

Thank you all for your contributions to this thread. Pat and Hnasr thanks again. I will use parts of both your suggestions. Paula nd Gustav thanks for the suggestions on the aggregation. Good ideas. Finally John appreciate the interesting way to identify checkbox and use a binary string to find values!