MS Access - what is the code for the sum of checked Yes/No boxes in a row?

Question

I've found plenty of help getting the sum for a column, but I don't want to retype my table -- it would take forever. I need the sum within one row. Can anyone figure this out?

Answer 1

Run a query on the fields that have the check boxes you want to count, then export the query results to Excel.



In Excel, create a COUNTIF function at the end of the first row that counts all occurrences of "TRUE" going across. Something like =COUNTIF(A1:Z1, "TRUE"). Then use Fill to put the formula on the rest of the rows.



A checked box in Access comes out as TRUE when you export the value to Excel, and unchecked comes out as FALSE. Good luck!

Answer 2

Are you trying to figure out how many checkboxes are checked as yes? If so, you should be able to use the COUNTIF command. However, I can't tell you how to evaluate what the value is for a checkbox flagged as yes. I would assume it is a boolean value of 0 or 1 in the related cell but not sure.



UPDATE:

Hmmm... I thought the original question was asking about Excel, but I see it mentions Access now. It may be different than COUNTIF in there. You may have to develop a query that checks the column your asking about in a table, and put that on your form or report instead.

Answer 3

sum ? maybe you mean count checked yes/no boxes in a row ?

checked box with yes/no value usually for table with fieldtype boolean, sometimes for null and not null value (in integer fieldtype).

I assume your fieldtype is boolean, use this query :

Select count(A.*) as TotalTrue, count(B.*) as TotalFalse from yourTable as A left outer join yourTable as B on A.yourbooleanfield=true where B.yourbooleanfield=false

-- this will generate result with two column, TotalTrue and TotalFalse



notes : untested on ms access, maybe ms access can't accept subquery like that, so you can try this query :

Select count(*) from YourTable where yourBooleanField=true union All Select count(*) from YourTable where yourBooleanField=false

-- this will generate 1 column with two records

Answer 4

4/13 EDIT:



To COUNT across you need a series of IF statments (Access spells it with two "i"s... IIF)

IIF([Field1]="Y" then,1,0)+IIF([Field2]="Y" then,1,0)+IIF([Field3]="Y" then,1,0)+etc...



Access normally stores Y/N fields as numbers -1 for YES and 0 for NO.

A inverted sum of these fields (([Field1]+[Field2])*-1) would tell you how many are "Y".



-----------------

Original Answer



In an Access Query You Total "across" like this:

[Field1]+[Field2]+[Field3]



If You add a TOTAL Field to your table You can run an UPDATE QUERY to set it's value for each record using the above expression in the "UPDATE TO" spot. Then adjust your input to Total as the records are being added. (put a textbox on your form with the above expression as the "Default Value").