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]
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 rowabsolutely agree! (ran short of time earlier)
Avoid Null issue and using NzAs 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.
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
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
Query: h_questions_queryID StdID Qtr yes Count
1 0 5
2 5635626345 0 3
3 9999999999 0 4
4 0 3
5 9999999995 0 3
tblQuestions should NOT contain StdIDI mentioned before "If I have to ignore normalization for a near future, I may use q1, q2, q3 …."
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;