count multiple fields of one table in one query

[Flora]

Hi,

I have a table with more than 10 option groups as fields. I want count the number of each fields with value not "No" in one query. The detail is as below:

option value: yes: -1, no: 0, n/a: 1 CNBD: 2
Fields: option A, option B, option C, ...........
0 -1 2
-1 1 -1
2 0 1
*************************
result: 2 2 3

Anybody know how to do the query? Thanks in Advance!

 

[John Spencer (MVP)]

SELECT Sum(IIF([Option A]=0,0,1)) as CountA,
Sum(IIF([Option B]=0,0,1)) as CountB,
...
FROM yourTable

In the query grid, do a totals query and put the statement as a calculated field
and then use SUM in the Totals cell
Field: CountA: Sum(IIF([Option A]=0,0,1))
Total: Sum

 

[John Spencer (MVP)]

Whoops! My error. I should have said.

Field: CountA: IIF([Option A]=0,0,1)
Total: Sum

Glad you found a solution.

Thanks very much! If I use SUM in the Total cell, it doesn't work. But if changes to Expression, it works well!

SELECT Sum(IIF([Option A]=0,0,1)) as CountA,
Sum(IIF([Option B]=0,0,1)) as CountB,
...
FROM yourTable

In the query grid, do a totals query and put the statement as a calculated field
and then use SUM in the Totals cell
Field: CountA: Sum(IIF([Option A]=0,0,1))
Total: Sum

Hi,

I have a table with more than 10 option groups as fields. I want count the number of each fields with value not "No" in one query. The detail is as below:

option value: yes: -1, no: 0, n/a: 1 CNBD: 2
Fields: option A, option B, option C, ...........
0 -1 2
-1 1 -1
2 0 1
*************************
result: 2 2 3

Anybody know how to do the query? Thanks in Advance!