Issue with Counts of Checkboxes in Query

[JamiB1979]

Hello All! I am needing a push in the right direction. A little background for my question.

I have this table and it deals with quality assurance. There are five questions that the QA department asks the caller. The answers are represented by checkboxes. I think there are 13 total checkboxes. A couple questions have three answers.

I need a summary report for a given time frame. That will have the company the caller works for, and a percentage for each answer to each question. I know how to get a time frame. That's easy. I know how to group by company. Again simple. I know how to use count functions but im stumped on how to do the counts on this. Here is what I was thinking. I need to almost run a query for each answer and do something like

select count(answercheckbox as count of answercheckbox)
from table


where checkbox = 1

What does everyone think?

Thanks all!

Jamie

[June7]

First, I presume the fields are Yes/No datatype. These fields hold either -1 for True/Yes or 0 for False/No. You could Sum the field but then have to deal with negative sign.

Ideally for ease of query, the table would be structured like: CompanyID QuestionID AnswerCheck (is this how you have it?), then query could be:
SELECT CompanyID, QuestionID, Count(AnswerCheck) AS CountOfAnswer
FROM table
WHERE AnswerCheck=True;
GROUP BY CompanyID, QuestionID

Instead of the GROUP BY query, try building a report and use its Grouping & Sorting with summary calcs features.

[JamiB1979]

Excellent! Thanks so much for the thought! I got the query below to work for one of the questions. I'll just create a query for each question like this.

Next question to this. I would now need to count up the checkboxes and divide it by the total amount of calls to get a percent. I would really like to get a running total with each query and then just use that running total field on the report. Is that at all possible with Access? I could always do another query where I sum up the counts. Is that a possible solution?


Thanks so very much for the help so far!!


Jamie

[June7]

I would have to work with the data to be sure what you could accomplish. Seems that report Grouping & Sorting should be able to present the data in almost any grouping you want without having to save multiple queries. And if you want all the questions on one report, multiple queries will be quite unwieldy. Reports can do aggregate calcs (sum, avg, count) in the group, page, report header/footer.