1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 BOTH yes and no! Query Question

Discussion in 'Business Applications' started by charlottes, Jan 31, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. charlottes

    charlottes Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    30
    Hi There!

    I am running a query to populate a report. I have some records in the table that indicate 'yes' and some that indicate 'no' in a JOINED? field.

    I would like my report to show me those records who haven't joined ('no'), so I put criteria 0 in the query, which is fine.

    However, I would like to keep a running tab of those records with 'yes' selected in JOINED? separately as just a statistics FYI in the report. I do not want to know anything else about these record besides how many have 'yes' under JOINED? field.

    Is it possible to make a report generate this?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    charlottes, welcome to the forum.
    Yes it is possible, Yes values are stored as -1.
     
  3. charlottes

    charlottes Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    30
    My problem is that it isnt working.

    The formula to count all 'yes' entries is : =-Sum([Joined?]) and it produces a 0.

    Suggestions?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    No it won't work like that, you need an extra column in a query that has as it's Heading
    Yeses: iif([Joined] = -1, 1, 0)
    That should give you a column with a value of 1 for yes's and 0 for nulls or No's which can now be counted, either in the query or in the report.
     
  5. charlottes

    charlottes Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    30
    Thanks for your help, just about have this solved!

    How do I deal with the problem of it asking me "Enter parameter Value" for Yeses

    It has the field renamed as Expr1: [Yeses]

    I was always under the impression that you had to have a corresponding field in the original table the query was drawing results from.. do I have to add a column in the table called Yeses? I will try this while awaiting your reply..

    Thanks! :)
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    See the syntax of what I posted

    Yeses: iif([Joined] = -1, 1, 0)

    Yeses is the name of the new Column, I assumed that Joined was the name of the Yes/No field in your table, if it isn't replace the Joined in the square brackets with the correct field name.
     
  7. charlottes

    charlottes Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    30
    Sorry! i didnt understand that you meant use the entire "Yeses: iif([Joined] = -1, 1, 0)" as a column title in the query. I have now created that correctly.

    So youre saying instead of adding a 0 as criteria under the Joined? heading in the query I can just use this one formula which will enter 1's for yes and 0's for no into a column Yeses.

    My questions from here are:
    - In the report it displays "1" instead the number of 1's. Im needing a count of all 1's.
    - I am getting all records in the report, but am only wanting those which are 0 (or, no) which is why I originally had a 0 as criteria under the Joined? heading in the query.

    Cant wait to learn how to fix this!
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Just add the 0 to the Criteria row of the New Column, that will only give you the zeroes.
    You can then create a new query based on this query and use Totals & Grouping to Count the records with zeroes, or leave out the 0 criteria from the first query and count both 1s and 0s.
    Or you can count them in the Report.
     
  9. charlottes

    charlottes Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    30
    How do I count them in the report? I have two textboxes and ones for yes's the others for no's and all they both show is 1.
     
  10. charlottes

    charlottes Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    30
    Finally figured it out. Made an extra column to count both yes's and no's in the query, then in the report inside of textboxes with the same query as the control source I typed:

    =Abs(Sum([No's]=1))

    Which totals what I wanted.

    Thanks for your guidance along the way OBP!
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1087677

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice