Query - count columns that contain "1" 1

[upplepop]

I'm not sure how to formulate this query. I have a table of clients and several attribute fields that contain either a "1" or a "0" (to identify yes/no). To keep it simple, I need to know the count of clients who fit more than one of those attributes.

Just a guess, but perhaps a query could add up the 1s and 0s for each client, and count the totals that are 2 or above.

Here is a sample of the table:

Table: Client

Name|Attr_a|Attr_b|Attr_c|Attr_d|
---------------------------------
Joe |  1   |   0  |   1  |   0  |
Jill|  0   |   0  |   1  |   0  |
Bob |  1   |   1  |   1  |   1  |

In the sample table above, the query would return the number "2" because 2 clients (Joe & Bob) have a "1" in at least two attributes.

Thanks in advance for your help.

 

[dhookom]

I would change the field name name to something other than name. Perhaps ClientName is a better name than name:

SELECT Count(*) as NumOf
FROM (SELECT ClientName FROM Client WHERE Attr_a+Attr_b+Attr_C+Attr_d >1);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Darrylles]

Hi,

dhookum; doesn't Access store boolean true as -1?

If so, try this:
[tt]
SELECT Sum(CountOfname) AS SumOfCountOfname
FROM
(SELECT Count(name) AS CountOfname
FROM client
GROUP BY name
HAVING Abs(Sum(attr_a))+Abs(Sum(attr_b))+Abs(Sum(attr_c))+Abs(Sum(attr_d))>1);
[/tt]

I'm sure that it can be done more elegantly though.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[upplepop]

Thanks for your help. It's working now.