Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    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. #2
    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. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65

    Post

    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. #4
    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. #5
    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. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    They are all in one table.

  7. #7
    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. #8
    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
    currently using SS 2008R2

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    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. #10
    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
    currently using SS 2008R2

  11. #11
    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. #12
    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. #13
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    65
    Thanks izy. It worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •