Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010

    Unanswered: Two field count query

    Good afternoon.

    I am very rusty on SQL and Access, so bear with me.

    I have a query where I would like to get a total count for two fields.


    table name: Mishap Data
    Fields: Days Away From Work and Job Restrictions

    I would like the query to return a count on how many records have a YES in either one of these two fields. One record might have a YES in both of these fields, but I don't want the query to count these as two.

    I figure this would have to be done with some sort of IIF cmd.



  2. #2
    Join Date
    Jan 2009
    Kerala, India
    Since both fields are YES/NO field you can solve this issue with two queries. Do the following:

    1. Create a SELECT Query (say Query1) with the source table and insert a column to add the values (Yes=-1, No=0) of [Days Away From Work] and [Job Restrictions] together, with the following expression:

      DaysJob: [Days Away From Work]+[Job Restrictions]
      Result: DaysJob will be -1 or -2 or 0 in records.

    2. Create a Total Query using Query1 as source and take a count of DaysJob Column.
    3. Use <0 in the criteria row to exclude 0 cases from the count. (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  3. #3
    Join Date
    Jan 2010
    Excellent. I will give that a try. Thank you very much for taking the time to respond.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Or in one query
    SELECT sum(iif(Abooleanvalue=true,1,0)) as ABVSUM,
     SUM(iif(Bbooleanvalue=true,1,0)) as BBBVSUM, 
     SUM(iif(Abooleanvalue=true OR BBooleanValue=true,1,0)) as BVSUM, 
     SUM(iif(Abooleanvalue=true AND BBooleanValue=true,1,0)) as BothBVSUM, 
     COUNT(ID) as NoRows
    FROM MyTable;
    replace the table and column names with your actual table and column names

    ABVSUM contains the number of columns where the first column (ABooleanValue is true)
    BBVSUM contains the number of columns where the second column (BBooleanValue is true)
    BVSUM contains the number of columns where both column (ABooleanValue OR BBooleanValue is true)
    BothBVSUM contains the number of columns where both column (ABooleanValue AND BBooleanValue is true)
    NoRows contains the number of rows in the dataset if you need the if you need to, say calculate a %'ge you have the totalnumber of rows, and the number of each BooleanValue analysis already in the query

    Personally I wouldn't rely on implementation tricks such as assuming that a boolean column maps to a numeric value. You 'should' use the preferred method accessing the value, which in this case is true or false. thats not to say apr_Pillai's suggestion is incorrect or wrong, just that true or false means your code is portable so won't break if Microsoft decide to change the way they map boolean values or if say you used a table from another db vendor within your Access application
    Last edited by healdem; 04-08-13 at 10:51.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2012
    Logan, Utah
    Another thought to keep in mind is that iif is a proprietary extension to Access and new to SQL Server 2012 only. If you were to port this to anything else, your code would not work.

    To avoid that possibility you could replace the iif's with a case statement.

             when daysaway = 1 and jrestrict = 1 then 1
             when daysaway = 1 and jrestrict = 0 then 1
             when daysaway = 0 and jrestrict = 1 then 1
             else 0 end
     from SomeTable
    Last edited by LinksUp; 04-08-13 at 13:54.

Posting Permissions

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