Counting yes/no fields

[mimottershaw]

Dear all,

I have seen this query posted in numerous places before, but I have been unable to locate a complete answer. I would like help please with counting a yes/no field in a query/report. I understand that Access counts a "yes" as -1 using the "sum" Total function, and I have seen suggested therefore that the solution is to multiply the result by -1. However I have not found a clear answer yet which explains where to enter the calculation in the query or report, and the correct syntax!

I am sorry, it has been a few years since I have used Access, so any help would be gratefully appreciated

Thank you

Mark.

 

[Scooterbug]

You can simply use the Count Total instead of sum. Just add the yes/no field, set the criteria to -1 and simply select Count in the total line.

 

[mimottershaw]

Thanks for replying so quickly. Unfortunately this doesn't seem to work. I followed it to the letter and the query now returns no results at all. I thought the criteria field was used to filter results? Have I misunderstood you??

Thanks again,

Mark.

 

[mimottershaw]

Hello again Bob!

I've also seen this solution posted. Sorry to be amazingly dense, but I'm not sure where to type it. All the query fields in Access 2007 seem really prescriptive and don't seem to accept calculations. Please spell it out as you would to a child!!!!!

 

[mimottershaw]

Hi Bob,

That worked, thanks, but won't seem to work when I have another field that I want to sort by. Specifically I want to count the yes/no fields for the various specialities (see previous messages). I get an error which says "You tried to execute a query that does not include the specified expression 'speciality' as part of an aggregate function." Speciality is definitely a field in my table, which I dragged down on to the query box.

Sorry to be a pest, but this is driving me mad!

Mark

 

[mimottershaw]

Hi Bob,

Nope, now it just counts the total number of "yes" responses and adds them to each speciality. This is SO frustrating, and I know you are trying really hard to help, and that makes it worse!

Ok, deep breath! Imagine I have two specialities: Respiratory and Cardiology. I have seen four patients, two in each speciality. For "Test A" one each from Respiratory and Cardiology have had the test ("yes").

I put your code into Query 1 as suggested and it gives a result of "mynewfieldname" = 2 - This is correct (1 + 1 From each speciality)

In query two my first field is speciality, set to Group By in the Total field. In my second field I have "mynewfieldname" from Query 1. Whatever I set the Total field to I get the wrong result. With the Total Field set to Group By or Count I get:

Cardiology 2, Respiratory 2. It is obviously counting all tests against each speciality, which seems kind of what I've asked it to!?

Thanks again, and I await inspiration!

Mark.

 

[ByteMyzer]

mimottershaw,
Could you post the following examples:
* raw table data (complete with table/field names), AND
* expected output results from the query

 

[mimottershaw]

Hi All (especially Bob!),

I think I have stumbled on a solution. I'm sure I've seen it posted before (possibly on here so apologies if I didn't spot it's effectiveness before), but failed to make it work. It seems easier than some of the other options, so I thought I might post it for your bank of solutions.

[Bob, I'm sure it was your name I've seen put to this solution before, so I'm not trying to take credit!]

In the end, what I did was put "Expr1: Sum(Abs([FieldName]))" in "Field", and change the "Total" (if it doesn't change automatically) to "Expression." That works perfectly, and does exactly what I need.

Thanks again for all your help, patience and perseverence! I'm sure I'll be in touch soon!!

Regards,

Mark.

 

[brian.mccreery]

spot on....works perfectly