Query: counting certain values

[tomswaelen]

I have a pretty big main table, with lots of colums, containing mostly Yes/No fields. Is it possible to make a query that simply counts the number of Yesses or Nos in multiple columns? For example, I want to get the number of Yesses in columns A, C, and E.

I don't know any VBA, so if it is possible through a query or a formula, I'd like to do it that way.

 

[John Big Booty]

Have a look at a Totals Query and use the Count Option.

 

[tomswaelen]

I did that before posting this, it somehow doesn't do what I want it to. I want to be able to count the number of Yesses in columns A, C and E of row 1; the number of Yesses across multiple columns/fields for just one record, basically. If these all contain Yes, the result should be 3.

I can't find how to do that with a query.

 

[John Big Booty]

You will need to have each of your Fields (columns) in your query twice. You will use the Group By option one one instance and the Count option on the other instance.

 

[Brianwarnock]

As I understand it the poster wants to add horizontally, thus if they are true yes/no fields
countyes: -(field1+field2+field3)
countno: 3-(field1+field2+field3)

Brian

 

[John Big Booty]

As I understand it the poster wants to add horizontally, thus if they are true yes/no fields
countyes: -(field1+field2+field3)
countno: 3-(field1+field2+field3)

Brian

Oh that's not how I read the OP

 

[Brianwarnock]

Yeah well posts 1 and 3 do conflict, I took the latter to be a clearer explanation.

To count values in multiple columns across records in one query I use the
Sum(IIf(field=value,1,0)) approach

Brian

 

[bparkinson]

Yeah well posts 1 and 3 do conflict, I took the latter to be a clearer explanation.

To count values in multiple columns across records in one query I use the
Sum(IIf(field=value,1,0)) approach

Brian

I second this approach.

 

[Brianwarnock]

I should have added

but in the case of yes/no fields one can use

sum(-fielda) as countoffieldayes
sum(1+fielda) as countoffieldano

Brian