Results 1 to 10 of 10

Thread: Query question

  1. #1
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98

    Unanswered: Query question

    Hi,

    I have the following fields in my query:

    - Date (per month)
    - Text 1
    - Checkbox 1 (number)
    - Checkbox 2 (number)

    What I want to do is to calculate how many 'yes' are in checkfield 1 and how many in checkfield 2. In the criteria field I placed True for both of the checkboxes. Thing is, it doesn't calculate the values for each field seperately, but it always shows the same number.

    Am I doing something wrong? Is it better to do this using DCount, and if so, how can I do this?


    PS: I have translated 'number' from dutch to english (aantal) so I might have translated it slightly different than is shown in your access version

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Use an iif...

    select field1, field2, sum(Iff([Check1],1,0)), Sum(Iff([Check2],1,0))
    from table
    group by Field1, Field2

    Note: No where!

    Hope this helps....

    Greetz

  3. #3
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    Hi,

    I have tried it but it doesnt work. I have placed the sum-functions in seperate fields but I get the anouncement that I am trying to run a query in a static function. Please could you let me know where to place what part of your code?

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Talking

    just replace field1, field2 and table by the stuff you need. It should work.

    Just take the SQL as posted, (Copy it)
    Now create a new query (dont add anything)
    Go to SQL View
    Paste the SQL
    Replace the fields and table name.

    Run it.

    It works i am sure... cuase "The Mailman allways delivers"

    Greetz

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In this case, the mailman delivered a syntax error.



    sum(Iff([Check1],1,0)), Sum(Iff([Check2],1,0))
    Try:
    sum(iif([Check1],1,0)), Sum(iif([Check2],1,0))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Sorry typo (twice ! can you believe the donkey ?!)

    Tho i started of right
    Use an iif...
    LOL

  7. #7
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    Hi guys,

    The part with the Sum and IFF seems to work, the only thing is that if I add any of the other fields into it, i get the message:

    "You are trying to run a query for which Field1 is not a part of the static function"

    Anyone have any ideas on this?

  8. #8
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Your not grouping, click the SUM sign when you are in query design...
    Then change on the totals line, where it says sum() in the column line, to Expression

    Or take below query (corrected) and copy paste as described above)

    select field1, field2, sum(Iif([Check1],1,0)), Sum(Iif([Check2],1,0))
    from table
    group by Field1, Field2

    Greetz

  9. #9
    Join Date
    Dec 2003
    Location
    The Netherlands
    Posts
    98
    IT WORKS> thankyou so much. This is sooo helpful.

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Happy to help a fellow dutch(wo)man
    Last edited by namliam; 06-29-04 at 06:13.

Posting Permissions

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