• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

Analyzing/printing questionnaire results with MS Access

The questions are in a table Questions with following fields:
Question ID

Respondents' answers are in a table Respondents with the following fields:
Ans2 LU (etc thru 18 answers)
Answers  1-12 are linked to a 4 part Likert LookUp table
Answer 13 is linked to a 3 part LookUp table
Answers 14-18 are open ended

This arrangement is fine for the data-entry form, but I'm having trouble getting a relationship to the questions and building a crosstab query.
I think my db design is flawed.
1 Solution
Patrick MatthewsCommented:
>>I think my db design is flawed.

It is.  You'd have been better off having one "answer" per row in a related table.
WIthout such a single record, you may be able to get around this issue with a meta data layer

New table completely -

Question ID
Question #
Question Response Source  -  sql constructs showing which column to get the answer result

With the meta table built - you can then build a parser for each of the 18 questions each one reading the Question / Response Source column for each answer set.

With the parser and meta data you will then be able to write a final query that glues all the queries together in  a single record - see caveat below

I supposed it depends how many surveys you have loaded, and whether or not the surveys will be captured using this system in the future.  If not many, then hand crank each of the records from the database and perform manual lookups.

What ever the option - you will need to write / define a logic parser to process this .  Once done you will then be able to create a single entity containing answers and questions

Caveat regarding single record
I personally would not recommend this a single record.  A simple table structure as follows will be suiffice


Question # (1-18)
Question Text
Response ID
AnswerID (1-18)  -- duplicates Question # - just to demonstrate.
Answer Text

With that simple table structure in place your crosstab will be a breeze

Good luck

JudithAAuthor Commented:
I found my solution with Duane Hookom's At Your Survey database:

Thank you to everyone that responded.  It was a lot more complicated than I thought when I started out - but then, nothing ventured, nothing gained!

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now