Counting yes/no occurances

[reglarh]

I have 11 yes/no fields in a record, with about 600 records in the table.

I have a form with all 11 fields on a single line for each record, togther with a numeric field on the form that has to hold the count of yes/no fields set to a yes status. This is also recorded on each record. I have no idea on how to count these, when to count then and how to update the count field on the record.

Help would be appreciated!

Thanks

 

[MarkK]

Note that Yes has a numeric value of -1, and No has a numeric value of zero. So I would never store this count, I would always calculate it using a formula like . . .

YesCount: Abs(fld1 + fld2 + fld3 + , ... , + fld11)

. . . so you never have to bother to store the count, and when you retrieve it, it is calculated immediately and will never be incorrect.

 

[reglarh]

Many thanks.

So where would you put this code and when would it be executed? Would it be a calculated field within the table or an expression within a query?

I defined a count field in the table and created a form to capture the data. On each click on the yes/no box i either added or subtracted 1 from the count. It works but needed 11 event procedures to update the total field. Long winded but it works.

 

[MarkK]

I defined a count field in the table and created a form to capture the data. On each click on the yes/no box i either added or subtracted 1 from the count.

That's exactly what I would recommend not doing. Try this instead: Create a table structured like this . . .

tTestBooleans
ID ( autonumber, PK )
bln1 (yes/no)
bln2 (yes/no)
bln3 (yes/no)
bln4 (yes/no)

. . . then create a query with this SQL . . .

SELECT ID, bln1, bln2, bln3, bln4, Abs([bln1]+[bln2]+[bln3]+[bln4]) AS blnCount
FROM tTestBooleans;

. . . and open that query. Now start clicking the check boxes. See how the count updates automatically? Now, use that query, or one like it, to power your form.

 

[reglarh]

Many thanks. That works in query datasheet mode, but when I changed my report to reference the calculated total I just get #Name?

I copied my query and added the calculation code - all fine. Works exactly as you said.
I copied my report, deleted my database field, deleted all my event procedures, added the calculated field from the query - no joy!

Do you have any ideas?

I understand how much easier your approach is. My problem is that I was brought up on procedural languages and appreciating the different approach with Access takes a time. Also if I need to produce many reports including this count field, which I do, i always decide it's easier to store the calculated field for easy reference. i suppose your response would be to create a query referencing all fields in the table as well as the calculated field and use that in place of the table.