Survey table design, have searched require clarification.

[webmonkey]

Firstly hello, I'm new!

Sorry for creating yet another thread on table design/relationships for surveys, I did search just needed a bit of clarification.

From what I understand from my search the best way to create a basic db for surveys would be a question table, response options table, and response table, this is similar to a database I have implemented for a simple online surveying system. Now this works with Y/N, or even limited response questions.

However I can't get it to fit with my current project, and I don't think it will, but I thought I'd check to see if I'm being stupid.

I have a survey, 20ish questions expecting 200-250 responses.
The questions consist of a mixture of:

• Yes/No answers
• Select all that apply answers
• Select one from list
• Select one from list or select Other and enter it yourself
• Text/Comments

Because of the wide range of values, some completely unique and the possible number of answers per question per survey it seems the above method (and that implemented in At Your Survey which i've had a look at) would not work.

I only need to store responses (not questions), so I'm proposing a main table with related tables for those questions that require it.

So in essence a flat file response table with each field being a different question, each record being a separate survey response.
For those questions with multiple answers a separate table, related to the main response response table.

Data entry will be twofold, a web version of the survey automatically recording responses and manual entry into custom form within access for those paper returns.

Reporting will be faily widespread with a lot of cross referencing.

I know this is not a reusable format, and it is pretty much a one time only project anyway, relatively short lived. But I'd still like to create the most efficient product I can, within the time constraints.

Any views or help would be much appreciated

 

[Niniel]

I don't understand how you cannot store the questions - how do you want to know what the answers mean without a question?
But anyway, I believe you could and should keep your old design, especially since you want to do a lot of reporting and cross-referencing.
Yes/No questions are essentially questions like any other, except that the reply is not "Great", "Good", "Bad" or "Awful", but "Yes" and "No". Select All That Apply questions are lists of Yes/No questions. The ones with "Other" are a bit tricky, but basically, "Other" is just another possible answer; you'll just have to find a way to accommodate the user input.