Count number of checkboxes per recorded

[jlg5454]

Hello:
I have a report that needs to show the total number of checkboxes that are checked per recorded. There are a total of 17 checkbox options that can be checked per recorded. Is there a quick and easy way to do this in the report footer. Thanks for any assistance that you can provide.

 

[dhookom]

Do you want to total vertically, horizontally, or both?
To total a single Yes/No field in a report or group footer, then use an expression like:
=Sum(Abs([YesNoField]))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jlg5454]

Thanks for your reply

the query from which the source of this report is from contains a date field (which will be group into month), a reference number field , agent name and 17 check box options which are used for tracking error purposes. I want to create two reports, one to count the number of checks for each check box option for each month and number checkboxes check per record for each month. Thanks for any assistance.

 

[traingamer]

Use Duane's answer for your first problem.

One way to solve the second part is to create an expression in a query which sums all of the fields in question and use that in your report like:

... sum(abs([chkfld1]) + abs([chkfld2]) + abs([chkfld3]) + ...) as AllChks ...

traingamer

 

[dhookom]

jlg5454,
You should consider normalizing your table structure. A normalized application would make querying much easier. You would not have to calculate across a single record. Simple Sum(...) would work in most queries/reports.

Also, you have 17 possible errors. What happens to your application when someone wants to track 1 or 2 more types of errors or perhaps remove a type of error? I would hate to maintain a system where you had to add controls and update expressions to accomodate simple tasks like adding another error type.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jlg5454]

Thanks guys. I think I'm going to take a look at both of your suggestions. Train's suggestion does work for a short term solution. However, when I break down my table to separate the check boxes into another table, what common field would I be able to link it with to the other table to create queries and reports.

 

[dhookom]

Whatever the primary key is on your main checkbox table is would be the foreign key in a related table. Your new "error" table should have only 2-3 fields unless you feel you need to track another attribute of the error.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]