Counting Checkboxes

[maxx102]

Hello everyone, I've tried consulting the ever mighty Google and I can't seem to find the answer that I am looking for.



I have a query that I am trying to create that counts the four different safety teams at my corporate office. We track the building, the floor, and the four teams based upon the team member's cubical location. The query displays the building and floor correctly but it doesn't display the 4 safety teams correctly.

I am using checkboxes to select whether someone is on a particular team or teams. Is there a way to have the system only count the check boxes and disregard the empty boxes? based upon what I am seeing, it seems my query is counting the boxes regardless if the box is checked or not. From what I am seeing online, I guess I can do it using SQL but I am not sure how.....

How can I set this up using the Design View mode of the query I am using?

I am also using Access 2010 edition if that matters any.

[NTC]

The correct answer depends on whether you have an Option Group (of check boxes) or whether you have individual checkboxes.

With individual checkboxes, when they are checked, their value = -1. And so you need to sum rather than count. You can flip flop to a positive number display using an Absolute Value function abs()

With option groups then each check box will make the option group control have a value; probably 1-4. you would need to count the 1s vs 2s vs 3s vs 4s.

Hope this gets you going in the right direction.

[maxx102]

I think they are individual checkboxes. Below is what is shown in "SQL view".


SELECT [Safety Teams Database].[Building Location], [Safety Teams Database].Floor, Count([Safety Teams Database].[CEAT Floor Captain]) AS [CountOfCEAT Floor Captain], Count([Safety Teams Database].ERT) AS CountOfERT, Count([Safety Teams Database].WARN) AS CountOfWARN, Count([Safety Teams Database].CERT) AS CountOfCERT
FROM [Safety Teams Database]
GROUP BY [Safety Teams Database].[Building Location], [Safety Teams Database].Floor
ORDER BY [Safety Teams Database].[Building Location], [Safety Teams Database].Floor;