# Thread: How to count incidence from 10 fields?

1. Registered User
Join Date
Oct 2003
Location
Posts
65

## Unanswered: How to count incidence from 10 fields?

Hi!

I have 10 fields that is either a "yes" or a blank. I need to know how can I check if there's more than 1 field(out of the 10) that has a "yes" entry.

thanks.

2. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713

## Re: How to count incidence from 10 fields?

Originally posted by Jigsatics
Hi!

I have 10 fields that is either a "yes" or a blank. I need to know how can I check if there's more than 1 field(out of the 10) that has a "yes" entry.

thanks.
Well from the rather vague question, there are numerous ways to do this ...

3. Registered User
Join Date
Oct 2003
Location
Posts
65
Sorry...

Here's the situation:

I have 10 fields representing 10 establishments. Each of them can either be a "yes" or a blank. I have another field(MainTourism) that contains the main establishment. The MainTourism field should only be filled in if they have more than one establishments checked(meaning there should be more than 1 field that contains a "yes"). How can I do this in a query?

4. Registered User
Join Date
Jan 2004
Location
Jersey, UK
Posts
108
Create new fields for each of the 10 establishments and in those create iif statements looking for a "yes" and if true, returning the value of 1.

All the remains is to sum those fields. If the result is greater than 1, then... else...

Does that help?

Regards - Andy

5. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally posted by Jigsatics
Sorry...

Here's the situation:

I have 10 fields representing 10 establishments. Each of them can either be a "yes" or a blank. I have another field(MainTourism) that contains the main establishment. The MainTourism field should only be filled in if they have more than one establishments checked(meaning there should be more than 1 field that contains a "yes"). How can I do this in a query?
Where are these 10 fields? In ONE table? multiple tables?

6. Registered User
Join Date
Oct 2003
Location
Posts
65
They are all in one table.

7. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally posted by Jigsatics
Sorry...

Here's the situation:

I have 10 fields representing 10 establishments. Each of them can either be a "yes" or a blank. I have another field(MainTourism) that contains the main establishment. The MainTourism field should only be filled in if they have more than one establishments checked(meaning there should be more than 1 field that contains a "yes"). How can I do this in a query?
SELECT COUNT(IIf(Check1=True,1,0) + IIf(Check2=True,1,0) + ... + IIf(Check10=True,1,0)) AS Expr1 FROM MyTableNameHere;

8. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
i'm not so sure about the COUNT()

isn't it more like:
SELECT * FROM blah WHERE (iif(check1,1,0) + iif(check2,1,0) + ....
+ iif(check10,1,0)) > 1;

...but that's ugly too.

izy

9. Registered User
Join Date
Oct 2003
Location
Posts
65
I tried your SQL query and all I got is a column EXPR1 with the number 35927 which corresponds to the total number of records in the table. I was expecting a value for every record but I only got one.

10. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
it's probably time for a little clarification about what you are trying to do.

M Owen's COUNT will ...produce a number
my WHERE should produce all records where more than 1 check is checked.
your last post suggests you want to produce all records where more than 1 check is checked and show the number of checks checked.

what do you really want? izy

11. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally posted by Jigsatics
I tried your SQL query and all I got is a column EXPR1 with the number 35927 which corresponds to the total number of records in the table. I was expecting a value for every record but I only got one.
You didn't SAY Thaaaaaat .... Drop the COUNT() from the query then ....

12. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Jig,

Please also note that you will need to provide the appropriate filtering etc ... for the query.

13. Registered User
Join Date
Oct 2003
Location