Access query problem

[cdborg]

Good Morning,

I have a survey database where for the most part one question represents one field in the table.
For several questions multiple fields represent the same question. For example there is a question
What type of temporary accommodation have you used? (on a form). The user is then able to select
more than one answer. This question is represent in the table by the following YES/NO fields:
Q6Shelter Q6Freind Q6University Q6YWCA ect...

I need to count the number of yes answers, and the number of no answers for each part of question 6.

So the query should look like

Q6Shelter Count Q6Friend Count Q6University Count
yes 12 yes 54 yes 21
no 14 no 67 no 7
How does one accomplish this? Thanks for you time

 

[lespaul]

what is the structure of the table where the answers are stored?



Leslie

 

[cdborg]

Most of the questions represent one field in the table.
I'm at work now so I and don't have access to the database
so I'll try to explain as best as I can.

There are 29 questions in the survey, for the most part
the field name represents the questions name. For example
Question on is called Where are you from? In the table this
field is called Q1WhereFrom. I'm having problems with
the questions that are represented by mutiple YES/NO fields.

Question 6 in the suvery is entitled:

What kinkd of temporary accommodation have you used?

University YMCA
Dorm Shelter

On a form the user can check multiple answers via
YES/NO fields. This are stored in separate fields in
the table. I need a query or report that can count
the number of YES and NO answers for each YES/NO field.
Does this make sense?

 

[dhookom]

Yes/No values are stored as either -1/Yes or 0/No. You can sum the field to get the negative of the -1/Yes answers. Sum the Absolute Value to fix this
NumOfYes:Sum(Abs([YourField]))
or
Field: NumOfYes:Abs([YourField])
Total: Sum

To get the number of No answers, add 1 to the field and sum
NumOfNos:Sum([YourField]+1)
or
Field: NumOfNos:Abs([YourField]+1)
Total: Sum


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lespaul]

ok if you will provide some examples of:

This are stored in separate fields in
the table. I need a query or report that can count
the number of YES and NO answers for each YES/NO field.

Most survey database are set up more along the lines of:

tblQuestion
QuestionID
QuestionText

tblAnswers
AnswerID
QuestionID
Answer

tblSurveyAnswers
SurveyID
AnswerID

now I can get the question from the answerID in tblSurveyAnswers since tblAnswers.AnswerID contains the question it belongs to. In your case I would expect:

tblAnswers
AnswerID QuestionID Answer
1 16 Yes
2 16 No
3 17 Yes
4 17 No

So in tbl SurveyAnswers if I had:

tblSurveyAnswers
SurveyID AnswerID
55 2
55 3

would show that question 16 was answered NO and question 17 was answered YES.

I'm having problems with
the questions that are represented by mutiple YES/NO fields.

Question 6 in the suvery is entitled:

What kind of temporary accommodation have you used?

University YMCA
Dorm Shelter

I wouldn't expect this to have multiple yes/no fields. The question is : What kind of temporary accomodations have you used. The answers are :

University
YMCA
Dorm
Shelter

There are no Yes/No answers here! There may be Yes/No answers on the paper form you are trying to convert(???), but you need to look at what data you are trying to collect.

As far as collecting the information you need:

Q6Shelter Count Q6Friend Count Q6University Count
yes 12 yes 54 yes 21
no 14 no 67 no 7

something like this might work, but if your structure was better organized and designed properly (see 'The Fundamentals of Relational Database Design'), this would be much easier:

SELECT Q6Shelter, Count(Q6Shelter) As CountOfQ6Shelter, Q6Friend, Count(Q6Friend) As CountOfQ6Friend, Q6University , Count(Q6University ) As CountOfQ6University From TableName Where Q6Shelter = 'Yes' and Q6Friend = 'Yes' AND Q6University = 'YES'
UNION
SELECT Q6Shelter, Count(Q6Shelter) As CountOfQ6Shelter, Q6Friend, Count(Q6Friend) As CountOfQ6Friend, Q6University , Count(Q6University ) As CountOfQ6University From TableName Where Q6Shelter = 'No' and Q6Friend = 'No' AND Q6University = 'No'

Leslie

 

[dhookom]

To augment Leslie's great recommendation, you might want to check out the "At Your Survey" demo at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This might be more work than you need at the moment but it will show you how to normalize a survey app.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.