I am working on a Survey / Questionnaire database and have hit a roadblock. I am trying to follow a design that mirrors the At Your Survey database in Roger's Access Library that has been recommended on these forums.
Attached is a .jpg (zipped since forum says new users can't post images) with the current relationship structure that I have. I have removed the Surveys table as there isn't a need for more than one survey any time soon.
Many of the survey questions are set up in sets like below (example only). Each question (Fruit 1-3) uses the same list of possible responses and the responses need to be ranked (Fruit 1 = most preferred fruit, Fruit 3 = Least preferred).
List Your Top Three Fruits
Fruit 1: Apple
Fruit 2: Pear
Fruit 3: Orange
Because of this, I have set up an extra table with question categories. Otherwise, I would be storing a lot of redundant values if I had a table with only the QuestionID and Possible Responses (some sets of questions will ask for as many as ten user responses that all use the same set of possible responses)
I want to be able to join the Possible Responses table (SrvQPosResponses) to the table where the Survey Responses are stored (SrvResponses).
Here's where I'm confused. The At Your Survey database does not join these two. Don't I want to join them so that if I change a possible response, it will be updated in the survey responses as well (say if I want to rename Apple to Apples). If the answer is yes I do, how do I set it up best given that some questions will be open ended (user input values) and others will have possible responses?
Should the Survey Responses table have two columns? One that stores a Possible Response ID (joined to the Pos. Resp. table) and one that stores user input values? Then on the entry form have it store the response to the question in the appropriate column? (NOTE: questions that are open ended will not be categorized and would not have a QstnCatID). Is there a better method?
Does the overall Relationship Structure look good?
Thanks in advance,
-Eric
Attached is a .jpg (zipped since forum says new users can't post images) with the current relationship structure that I have. I have removed the Surveys table as there isn't a need for more than one survey any time soon.
Many of the survey questions are set up in sets like below (example only). Each question (Fruit 1-3) uses the same list of possible responses and the responses need to be ranked (Fruit 1 = most preferred fruit, Fruit 3 = Least preferred).
List Your Top Three Fruits
Fruit 1: Apple
Fruit 2: Pear
Fruit 3: Orange
Because of this, I have set up an extra table with question categories. Otherwise, I would be storing a lot of redundant values if I had a table with only the QuestionID and Possible Responses (some sets of questions will ask for as many as ten user responses that all use the same set of possible responses)
I want to be able to join the Possible Responses table (SrvQPosResponses) to the table where the Survey Responses are stored (SrvResponses).
Here's where I'm confused. The At Your Survey database does not join these two. Don't I want to join them so that if I change a possible response, it will be updated in the survey responses as well (say if I want to rename Apple to Apples). If the answer is yes I do, how do I set it up best given that some questions will be open ended (user input values) and others will have possible responses?
Should the Survey Responses table have two columns? One that stores a Possible Response ID (joined to the Pos. Resp. table) and one that stores user input values? Then on the entry form have it store the response to the question in the appropriate column? (NOTE: questions that are open ended will not be categorized and would not have a QstnCatID). Is there a better method?
Does the overall Relationship Structure look good?
Thanks in advance,
-Eric
