How can I count each possible response in a report to each questio

[ran]

I have a MS Access 2000 database that I am trying to use to tally paper
surveys regarding how people liked a particular event/activity. The database
consists of 15 tables. One table, ActivityTBL, holds details concerning the
activity/event, (time, place, facilitator, subject, etc.). This table is
linked to 14 other tables.



Ten of these tables relate to questions on the paper survey. Each of the 10
tables has 3 fields, a primary key, an event Id field (that links it to the
event detail table, ActivityTBL) and the answer field (number data type) to
that particular event being surveyed by a particular survey-taker/survey
participant. Survey participants may answer the first 10 questions by
choosing a number; 5 to 1. This corresponds to answers ranging from Strongly
Agree to Strongly Disagree to Not Applicable. The other four tables (memo
fields) are for comments people can write in on the paper survey. All tables
are linked by an Event ID field.



I have created a form, with a sub form for inputting the data. This works
well.



My problem is how can I count each possible response in a report to each
question. That is to say I have 10 tables each representing 10 questions.
The range of possible answers for each question could be:

5- Strongly Agree

4-Agree

3-Disagree

2-Strongly Disagree

1-Not Applicable



I need to be able to create a report that counts each possible answer for
each question. Something like this:

Question One Count

5- Strongly Agree 4

4-Agree 5

3-Disagree 6

2-Strongly Disagree 5

1-Not Applicable 0

Total Responses/answers 20



Question Two Count

5- Strongly Agree 6

4-Agree 4

3-Disagree 3

2-Strongly Disagree 8

1-Not Applicable 0

Total Responses/answers 21


How can I do this? Any comments on how to improve the table structure will
be greatly appreciated. Thank you

 

[John Spencer]

OUCH! Bad table structure. You would have been much better off with a table
structured like

QuestionID - Points to question (1 to 10 in your case)
EventID
QuestionResponse
(and perhaps a SurveyIDNo to point to one specific survey or person taking the
survey)

You best bet is to use a normalizing UNION Query to set up your data. I'm not
quite sure I understand how you current tables are set up - It appears you
have one table for each question. If that is the case, you will need to build
(in SQL View) a query that looks like the following

SELECT "Question1" as qTitle, EventID, Answer
FROM TableOne
UNION ALL
SELECT "Question2" as qTitle, EventID, Answer
FROM TableTwo
UNION ALL
SELECT "Question3" as qTitle, EventID, Answer
FROM TableThree
UNION ALL
....
SELECT "Question10" as qTitle, EventID, Answer
FROM TableTen

Once you have that, you can use the above query instead of the ten tables to
get the results you are looking for. Use the union query as the source for a
totals query.

SELECT EventID, qTitle, Answer, Count(EventID) as CountResponses
FROM UnionQueryYouBuilt
GROUP BY EventID, qTitle, Answer

For a good example of how to set up a survey database:
Duane Hookom has a sample survey database "At Your Survey" at

http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4

This fully functional application uses a small collection of tables, queries,
forms, reports, and code to manage multiple surveys. Users can create a
survey, define questions, enter pre-defined answers, limit to list, report
results, create crosstabs, and other features without changing the design of
any objects.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ran]

John:

Thank you for your reply. I appreciate it very much. It was very helpful.

If I create a view that unionizes all my question tables (yes I have a table
for each question) wouldn’t I have to do this for each new survey? Could I
create a stored procedure that did the same thing and use a parameter to
indicate my survey ID, which survey I wanted to report on?

I was a bit worried about my table structure. I did it this way because it
was an easy way to input responses. However after glancing at Duane Hookom's
"At Your Survey" I think I may scrap my database and try to create one
following Mr. Hookom’s design. I think it will be educational as well.

Thanks again
Randall