Selective Count

[Shaft]

Hi All,

I've created a table that take the results of a questionaire, with

0 = No
1 = Yes
2 = N/A

For each question I want to count the number of Yes, No and N/A in one query. I can get the result for one question by using Count with the critera set to = 0 or =1 or =2, but can't do it for multiple questions in one query and I don't really want to create 33 query for each answer and then join them.

I have attached the table that I'm using cos I'm not usually very good at explaining things.

Thank You

 

[Rich]

Use a Totals query without criteria

 

[FoFa]

sorry your table makes less sense. You don't have one column for each question do you?

 

[Shaft]

Use a Totals query without criteria

How's that going to work cos I only want to count the 0's the 1's or 2's at any one time. So if I count them all it's going to tell me nothing.

sorry your table makes less sense. You don't have one column for each question do you?

Each column is a question and each row is a questionaire.

 

[Mile-O]

Each column is a question and each row is a questionaire.

Rich's suggestion is the correct one but, based on your database, it will never be that simple because you have went ahead and created a database that does not conform to the rules of normalisation.

In the case of a database, you need a table for each entity. In your case, questions are entities, questionairres are entities, etc. Thus you need one table for questions, one for questionairres, etc.

I would do a search on surveys by posts from Pat Hartman as she has answered many questions on this topic.

By having your questions as fields you are making a fundamental error in database design: that your tables are growing wider (fields) when they should be growing longer (rows).

For example, should you have to add a new question to your database that would require you to add a new field, edit all relevant queries to evaluate this new field, edit your forms so that the new field can be bound, edit reports so that your new field can be displayed, and edit any VBA/Macros you may have.

By properly normalising, at least up to Third Normal Form, then all you would need to do is add one new row detailing the question.

 

[FoFa]

A totals query is not what you want, but since each question is in it's own row, you now have to basically create a query for each question, or handle it in VBA. Sj is correct, DB design impacts how you can access the information.

 

[Rich]

A totals query is not what you want,

Why not?

 

[FoFa]

Well maybe I misunderstand, but to me a totals query SUMS the data, not COUNTS the data.

 

[Mile-O]

to me a totals query SUMS the data, not COUNTS the data.

It depends which aggregation you chose.