Survey DB Modelling - Access

[iksotof]

Hi I am building a survey DB, with three different quesionnaires, say A, B and C. What is the best way to model, have a table with survey names in (tblSurvey) on a one to many with all the questions (tblSurvey) or three different tables for the questions?

Many thanks, Darren.

[HansV]

Hi Darren,

Welcome to Eileen's Lounge!

The "official" answer is probably that you should have a table tblSurveys with the survey names, and a table tblQuestions with the questions.
If there is any overlap between the questionnaires (i.e. if they have questions in common), you'd create a third table tblSurveyQuestions that lists the questions for each survey:

SurveyID	QuestionID
1	1
1	2
1	3
2	1
2	2
2	4
2	5
3	1
3	4
3	6

This table implements a many-to-many relationship between tblSurveys and tblQuestions.
This setup would allow you to analyze not only the individual questionnaires, but also how a "shared" question is answered across the surveys.

But on the other hand, if the questionnaires are completely different, you might simply create different tables for each of the questionnaires (my personal preference would still be for the setup described above.)

[iksotof]

thanks Hans, I was thinking pretty much that. It's been a while, nice to re aquainted and I must say, like the clogs!

[HansV]

I must say, like the clogs!

Thanks! Nice to see you here.

Count yourself lucky that you didn't see my previous user picture...