# Solved: Calculating Yes/No Fields in Access



## hotdiamond (Mar 4, 2004)

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


----------



## lika2know (Oct 10, 2007)

How about a query with a calculated field in which you use the function Count and select on value = YES....
lika2know


----------



## hotdiamond (Mar 4, 2004)

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".


----------



## hotdiamond (Mar 4, 2004)

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?


----------



## slurpee55 (Oct 20, 2004)

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.


----------



## hotdiamond (Mar 4, 2004)

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?


----------



## slurpee55 (Oct 20, 2004)

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.


----------



## hotdiamond (Mar 4, 2004)

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")


----------



## hotdiamond (Mar 4, 2004)

[LVEF_Test] is one of the field names.


----------



## jimr381 (Jul 20, 2007)

=count(if([lvef_test]=1),1)

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


----------



## slurpee55 (Oct 20, 2004)

Unless you have changed your defaults, it actually is listing them as 0 (no) or -1 (yes). Try counting one of those.


----------



## hotdiamond (Mar 4, 2004)

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.


----------



## slurpee55 (Oct 20, 2004)

did you look at the db I loaded? counts were easy there. Are you running that formula in a report or a query?


----------



## hotdiamond (Mar 4, 2004)

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.


----------



## slurpee55 (Oct 20, 2004)

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....


----------



## hotdiamond (Mar 4, 2004)

Let me see if I can....it's just so much confidential info I'd have to try to get rid of.


----------



## hotdiamond (Mar 4, 2004)

I also forgot to mention that it's not really a true yes/no field. I attempted to change it to that but the data had already been entered. It was originally set up as a text field in which an option group was created on the form. There were two buttons, yes and no. Yes was = to 1 and No was = to 2. Does that have anything to do with why my calculations aren't working?


----------



## jimr381 (Jul 20, 2007)

Ok since it is actually a text field (boo hiss)  try this.

=count(if([lvef_test]="1"),1)


----------



## slurpee55 (Oct 20, 2004)

another quick and easy example...
Jim, just have to count the text ="1" in a query....


----------



## jimr381 (Jul 20, 2007)

I thought that is what I was having him do in my function.  I will check out yours though as well. The key thing I missed before were the quotes since it was text field. I had assumed it was a yes/no field initially.


----------



## hotdiamond (Mar 4, 2004)

Sorry about that Jim.

But I still couldn't get it to work. When I type in your formulas, it doesn't recognize it as a formula. The text box continues to be unbound. Is the formula missing anything?


----------



## jimr381 (Jul 20, 2007)

Did you start it with an =? Within forms and reports you have to use the = and in queries you do not.


----------



## hotdiamond (Mar 4, 2004)

Yes, I actually just copied it from your quote.


----------



## slurpee55 (Oct 20, 2004)

Try this:
=Count(IIf([lvef_test]="1",1)) 
I tried this in a report and it worked....


----------



## jimr381 (Jul 20, 2007)

Slurpee forgot to close his brackets tisk tisk.  JK hehe. He was right though it is Iif in access though.


----------



## slurpee55 (Oct 20, 2004)

Ooh, you are quick - actually, I had another name for the field, and copied lvef_test from your post and accidentially pasted over the closing bracket....


----------



## hotdiamond (Mar 4, 2004)

I must be doing something wrong b/c I still can't get it to work. If you don't mind, can you tell me what the last one in the formula mean? Should it be something else maybe?


----------



## jimr381 (Jul 20, 2007)

It places a number as the result which it will be able to count.

It should look like this:
=Count(IIf([lvef_test]="1",1))

Make sure to not use squiggly brackets and to use brackets in the right places and parens in the right places as well. You should be able to copy that into the text box just fine.


----------



## slurpee55 (Oct 20, 2004)

Hotdiamond, you haven't told us where you are trying to run this formula, a query or a report. Depending on where you are working, there may be better or at least other ways to do it.


----------



## hotdiamond (Mar 4, 2004)

I was running the formula in a report.


----------



## hotdiamond (Mar 4, 2004)

Thank you both for your help.


----------



## jimr381 (Jul 20, 2007)

Congrats! If you have further questions then feel free to post them.


----------

