Calculating an average from checkboxes?

[Twitchie]

Greetings. I have a report that I'd like to create a scorecard within. The scores will be expressed as percentages. I have 8 categories of tasks, and each category has a differing number of activities within it. When an activity is completed, the user checks a box indicating it is done. So I'd like to calculate the percentage of a category. I've tried creating a control that has =Abs([Task_1_1_Flag]+[Task_1_2_Flag])/2 in the On Update and On Enter to just calculate the percentage on the fly when the report is accessed, but the field is blank when loaded despite 1 of the 2 checkboxes displaying as checked on the report. The percentages don't necessarily need to be stored in the table, but could be if that's easier. Much thanks!

[June7]

A checkbox can have a null state. Arithmetic with null results in null. Not sure that is the issue here, but try:

=Abs(Nz([Task_1_1_Flag],0)+Nz([Task_1_2_Flag],0))/2

Saving calculated data (data dependent on other data) is usually bad design. These checkbox entries are dependent data. Relying on user to remember to make this entry will be constant source of data inaccuracy and frustration. Whether or not an activity is completed could be determined with query and calculation.

[Twitchie]

Thank you very, very much!! This particular project is very reliant upon people checking the boxes - it's actually part of the task. I just needed a quick snapshot of the progress people have made across the 8 categories. It's been a little while since I've sat and worked in Access. I've learned some SQL and work in Oracle Developer now so I keep getting that in my head as how I would query something I wanted. Thanks again for the assistance. Much appreciated! And I will use the forum tool to mark this as solved.

A checkbox can have a null state. Arithmetic with null results in null. Not sure that is the issue here, but try:

=Abs(Nz([Task_1_1_Flag],0)+Nz([Task_1_2_Flag],0))/2

Saving calculated data (data dependent on other data) is usually bad design. These checkbox entries are dependent data. Relying on user to remember to make this entry will be constant source of data inaccuracy and frustration. Whether or not an activity is completed could be determined with query and calculation.