Calculating totals for each column in Access query

[jisque]

Appreciate any help in how to calculate the count of yes reponses along each column in an Acces query. I have a Responses table were each respondent answers ticks yes/no to each of five questions.
I have used something like SELECT Count([Q1]) AS Ques1, Count([Q2]) AS Ques2
FROM Questions
WHERE (((Questions.Q1)=Yes) AND ((Questions.Q2)=Yes));

But it only returns the number of the rows where both values are yes - I would like tthe count for each column seperately. CrossTab query does not work because I have 5 columns in all. Also tried Union query - it returns to correct number of counts but displays them in one column with no tile headings for the questions.

 

[shannonp]

The "Yes/No" variable (or True/False type field) takes on an integer value doesn't it?

Yes = 1
No = 0
(not entirely sure if this is how YES/NO values are stored...it might be -1 and 0, you may want to check the help system)

Therefore, would it make any sense to maybe just do a SUM and not worry about criteria at all?

Example:
Q1 Q2 Q3 Q4 Q5
Y Y N N N
N Y Y N N
Y Y Y N Y

is the same as...
Q1 Q2 Q3 Q4 Q5
1 1 0 0 0
0 1 1 0 0
1 1 1 0 1

so when summed together...
Q1 Q2 Q3 Q4 Q5
2 3 2 0 1

Just a thought...

 

[ConnellG]

For each Yes/No field in the query you wish to total, type:
(Alias Name): Sum([Field Name]* -1
Yes/No fields are binary, either on/off, true/false, etc. The value is stored as -1 for True. When you sum this field the No's don't count because they have a value of 0.
The *-1 at the end converts the total (which is a negative number) into a positive number.

 

[jisque]

Brilliant! Both replies. Thanks a million -you've solved a couple of sleepless nights

 

[jonhickman]

THX also helped me ;-)

 

[Golom]

Just to be picky ...
The solutions suggested do work but they rely on Yes = -1 and No = 0. It ain't necessarily that way in all Database systems (some have Yes = +1 for example). To protect yourself from those sorts of migration woes (if and when)

... , SUM(IIF([YesNoField]=TRUE,1,0)) As [YesCount], ...

gets you around relying on booleans having a particular numeric value.