Summing of Yes/No fields (1 Viewer)

[G Williams]

I have a form made up expressions based on fields in linked tables. The fields are all Yes/No fields. The form is used show when students have passed certain criteria for their coursework. I would like to be able to sum the individual Yes/No fields for each student record to find a running total of hw many Yes criteria they have achieved.

I am plodding around in the dark here, can anyone cast some light on the problem? - I have a nasty feeling that this is not using a database as it should be used!

 

[Carol]

Here is how I accomplished that problem. Use a select query and make a new field to separate your yes/no answers:
Yes: IIf([Field Name]=Yes,"Yes")
No: IIf([Field Name]=No,"No")

This will then separate your answers into 2 separate columns. You can then use a total line and use the count function to arrive at the total "yes" and total "no".

 

[Travis]

Most people miss the mathamatical logic that this problem presents. An easy method to use to sum them is to simply add them together.

Ok I now here everyone ask how do you add Yes/No (or True/False). Actually You only need to remember that False = 0 and True = -1. Of course adding negative numbers seems at first to be counter productive. Don't forget to use the ABS function (Absolute value). Take the ABS of the total a presto you have the total number of yes's met. (As the No's = 0 and anything plus 0 = itself).

Example:
the fields Calculation would be:

=ABS([Field1YesNo] + [Field2YesNo] + [Field3YesNo])

if Field1YesNo = True, Field2YesNo = False and Field3YesNo = False the answer would be 1.

I hope the math leason helps. (Enjoy the flip side)

 

[KDg]

can't help it -

Travis : (-1) + (-1) = -2

add them up and *-1 or ignore the sign

In built functions can be laaaaazzzzzzzzy - I'm learning C and trying to get by without them. It makes you a lot more cunning, forget they exist and you can get much more done - even if it does take twice the time...

[This message has been edited by KDg (edited 02-14-2000).]