Best table or solution for my database

[GabrielG]

Hello everyone ,
I have a little problem and I need some advice to setup a database. I
created a table with approximately 20 columns. The columns have an entry of
Yes or no based on a questionnaire from survey. My question is what can I do
to get a result of my table I would like to know how many "Yes" and "no" I
have. I don't know how to add them or get this information.

Note I would like to add them by columns not by row.
Thanks in advance

 

[Brian Kastel]

One suggestion: create a select query that uses aggregate functions:

SELECT Sum(Abs([Resp1])) AS SumOfResp1, Sum(Abs([Resp2])) AS SumOfResp2, ...
FROM tblSurvey;

Since Yes is -1, totaling the absolute values of the field will provide your
answer.

Counting the Yes values explicitly should work, too, but for some reason
when I tried that using criteria my query invariably returned the total
number of records.

 

[Tim Ferguson]

created a table with approximately 20 columns. The columns have an
entry of Yes or no based on a questionnaire from survey. My question
is what can I do to get a result of my table I would like to know how
many "Yes" and "no" I have. I don't know how to add them or get this
information.

That is largely because the db design is wrong. The twenty yes/no columns
should be twenty rows in an Answers table like this (* means primary key,
as usual):-

Answers
=======
*QuestionnaireID references the main Questionnaires table
*QuestionNumber references the Questions table, if you have one
Value true/false, reports the actual answer given.

so it looks like this:

QuestionnaireID QNumber Value
=============== ======= -----
Eric 1 Yes
Eric 2 No
Samantha 1 Yes
Samantha 2 Yes


It then becomes easy to count how many questions were answered, how many
yesses, how many said (Yes, No) and so on. As you have discovered,
designing big wide tables like spreadsheets does not solve many problems.

B Wishes


Tim F