Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: Counting of Monthly YES/NO query

    Hello, I have a table a bit like this with lots of individual first aid reports on that have come from a form with YES/NO boxes on it

    Date..... Cut Bruise Break
    01/02/08 YES YES NO
    01/02/08 YES NO NO
    05/02/08 NO NO YES
    15/03/08 YES NO NO
    16/03/08 NO YES NO

    And I want to end up with a table/query like this with the actual figures in it, broken down monthly

    Mth Cut Bru Brk
    Feb 2 1 1
    Mar 1 1 0

    Any help on how to do it (either using SQL or in Access' own query designer) would be really appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You will probably want to use the IIF function. In the Access query editor add the table, then add a calculated field like this:

    Cuts:IIf([Cut]="YES",1,0)

    To get the month, you can either use Month and MonthName or the Format function. Something like:

    Mth:MonthName(Month([Date]))

    Again add this as a calculated field in the query designer or in SQL it would be:

    MonthName(Month([Date])) As Mth

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    To get the month, you can just format the date:

    Mth: Format([Date],"mmm")

    Then group by this field (use the Totals button).

    Since a YES is a -1 and a NO is a 0, you can also just sum them up and get the absolute value:

    Cuts: Abs(Cut)
    Bruises: Abs(Bruise)
    Breaks: Abs(Break)

    And then select Sum for these columns instead of using Group By in the Totals row.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2007
    Posts
    7
    Thanks. I didn't know that Yes was -1. I did that and then multiplied by -1 to give me the actual amount. Thanks again.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    Mth: Format([Date],"mmm")
    I advise you use the Month() [and Year()] functions for your grouping actually.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Because it's quicker?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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