Totals of Check boxes marked yes in query

[TK03]

I am trying to get a report off a data based used for tech services. If parts fail under warranty the tech will click a text box set up for all the major parts.
I want to have the query gather only boxes that are checked which it does now bit I want to know how many of each checked box.
There are a total of about 20 check boxes and I need a count of each group of check boxes checked.
For instance;
Check boxes Blower, sensor, control, gas valve ETC.
Report how many of each group.


Thanks in advance.

[Gicu]

If the fields represented by the checkboxes are defined as true boolean fields (Yes/No) you can simply use Sum(Abs(BlowerChecked)),Sum(Abs(SensorChecked)), ...

[orange]

Please show us your tables and relationships with tables expanded to see all fields.

[TK03]

These are the check boxes along with a date and serial number all in one table.

Query Partial view

If I put "Yes" in any one of the fields I get only the ones checked yes. What I want is just how many in each field were check. IE, Supply Sensor 7, Return sensor 5, vent sensor 3, Flame sensor 9 ETC.
I thought I could keep it simple and just add something in the criteria to make this happen.
Thanks

[TK03]

Where does this get added? Tried in the criteria but got error
Sum(Abs(SupplySensorChecked))

[orange]

I'd like to see your table designs, and some sample data.

[Bulzie]

In your query design, click the Totals button, then in the Total: row select "Count" as the option on all the Y/N fields. If you want to limit records say by a date range, put that in the Criteria row for the date field(which you should not have named "Date" just fyi) and select Where in the Total: field. Get rid of the Record# and Serial fields since you are just getting total counts it sounds like.

[Gicu]

That was just a name I came up with, try Sum(Abs([Supply Sensor])), Sum(Abs([Return Sensor])), etc. You need to make the query a totals query (click on the sum - sigma- sign on the ribbon).

Cheers,

[TK03]

In your query design, click the Totals button, then in the Total: row select "Count" as the option on all the Y/N fields. If you want to limit records say by a date range, put that in the Criteria row for the date field(which you should not have named "Date" just fyi) and select Where in the Total: field. Get rid of the Record# and Serial fields since you are just getting total counts it sounds like.

I did this but it returned data for all fields equal to the number of checked boxes even if the box was not checked. I have 40 test records. I have I chose 4 test fields and checked 10 check boxes of each of the check box fields. The result returned was 40 of each of the 4 test fields instead of 10 of each.
I put yes in the criteria and it returned 0 results of any fields.

[Gicu]

TK03,
Sorry for the mishaps, it seems I haven't quite explained how this should work.
The boolean field (Yes/No) actually stores 0 for No (False) and -1 for Yes (True). The built in ABS function returns the absolute (positive) value of that, so summing them would give you the count: https://www.techonthenet.com/access/...umeric/abs.php

So to implement that you need to select Expression instead of Count in the totals row of your totals query and in the field row use something like that: Return_Sensor_Count:Sum(Abs([Return Sensor])),Vent_Sensor_Count:Sum(Abs([Vent Sensor]))..........

Cheers,
Vlad

[ssanfu]

@TK03

Be aware your field names are not the best:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

Also, be aware that "DATE" is a reserved word and shouldn't be used as an object name.
See Problem names and reserved words in Access



Bad: Record#
Bad : Serial Number (space in name)
Bad: Supply/High

[TK03]

Thanks Vlad, Works perfectly

[orange]

TK03,
Glad you have it resolved.
You can mark the thread Solved by reviewing this.

[Mickjav]

Sorry to but in but I think there is an issue with the design in that you are adding parts as fields in a table which you will run out of.

I don't know how your using you table but if it's for parts and workorders I would have a basic stucture like:

tblParts
tblPartsAssigned
tblPartsCategories

mick