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: Calculating Yes/No Fields in Access

Discussion in 'Business Applications' started by hotdiamond, Nov 14, 2007.

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

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Please help. I am trying to calculate a percentage of my yes's. I have several yes/no fields in which I'll need to calculate a rate. I would prefer to create a report that will calculate this for me. However, right now, I am really just looking for the easiest/quickest way to do this. So if I need to run a query first and base the report off the query, I'll do it. Please help ASAP if you can.

    Thanks,
    Tonya
     
  2. lika2know

    lika2know

    Joined:
    Oct 10, 2007
    Messages:
    33
    How about a query with a calculated field in which you use the function Count and select on value = YES....
    lika2know
     
  3. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Walk me through it b/c everytime I try it, it doesn't work. Since my field is a yes/no field, I can't get it to find "yes".
     
  4. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Okay, I have created several different queries. One query counted my 1's which are equal to "yes" (numerator). Then I created another query that counted all (denominator). Then I created another query that calculated the compliance rate (numerator/denominator). The queries gave me exactly what I wanted. However, when I try to create a report off of the queries, it won't let me b/c it says they are not related. What do I need to do?
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Attached is a really simple database with a yes/no field that is counted in the query.
    You could also count the ids, and get a percentage by dividing the one by the other.
     

    Attached Files:

  6. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    It won't let me create one report for all of the queries b/c they are from the same table. What can I do?
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    There are several things - frankly, I would just make a query that pulled all your other queries together and base the report on that, or, you could make a report based on nothing or one of the queries and insert the others as subreports. If you want to zip and post your db (replace any data, if it is confidential, with nonsense data) and let us know exactly what you want in the report, you will probably get several replies, which may offer several answers.
     
  8. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    I was thinking about adding each query as a subreport but that will be too time-consuming. I have decided to run a report and just do the calculations in the report. But now I've gotta figure out how to count my "yes" in the report, which are actually showing up as "1". Any advice?

    I tried this formula but I must be doing something wrong:
    =Count([LVEF_Test])="1")
     
  9. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    [LVEF_Test] is one of the field names.
     
  10. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    =count(if([lvef_test]=1),1)

    But I thought a simple =count([lvef_test]) would work as well.
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Unless you have changed your defaults, it actually is listing them as 0 (no) or -1 (yes). Try counting one of those.
     
  12. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    jim, I tried your formula but it's not working either. It doesn't even recognize it as a formula. Is something missing?

    Slurpee, I have tried to use both 1 and -1 but it still doesn't count correctly.
     
  13. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    did you look at the db I loaded? counts were easy there. Are you running that formula in a report or a query?
     
  14. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    I'm trying to run the formula in a report.

    I did look at the sample database, but you ran a cross-tab query and my information will not let me run that type of query.
     
  15. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hate to sound like a broken record, but zip and load a dummied up database. I'm not bad with Access, Jim is better, and then there are others we can get involved, like OBP, rollin, bomb, AJ.... ;)
     
  16. 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...
Similar Threads - Solved Calculating Fields
  1. Koroshiya
    Replies:
    4
    Views:
    418
Thread Status:
Not open for further replies.

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

  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