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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.