Survey relationships & table structure

[kbrown]

I am trying to refine my database structure based on the information I have read in this forum about surveys and database design. The more I learn, the more complicated the database design becomes. I am not an access expert and have been teaching myself VBA over the last few months to accomplish more advanced tasks in the database.

I have 3 surveys:
Survey1: one for pregnant women (Program3)
Survey 2: one for children 1 year-3 year (Program1) and 3 year - 5 year (Program2)
Survey 3ne for infants birth-1year (Program1)

Each person will take the survey many times. Children in program 1 could take Survey 3 a total of 6 times and Survey 2 a total of 5 times. Then that child could move into Program2 and take Survey 2 three more times. Survey 1 is the simplest. One woman could take the survey 3 times while she is pregnant, then an indefinite number of times while she is breastfeeding.

My understanding is that I should have one table that holds all the questions for the 3 surveys. I would have these fields:
tblQuestions
QuestionID
SurveyID
QuestionNumber
Question Text
OptionGroup

Survey 2 and Survey 3 share 7 questions in common. Do I need to break this table apart further so that these 7 questions can have more than one SurveyID? Can someone give me an example of what this might look like, as I am having difficulty conceptualizing it?

I also have a table for response options. I have a similar question here. Two of the options are shared by Survey1 and Survey2. In addition, these two options (plus 4 other options) must allow for multiple selections. I am having trouble bending my mind around how to set this up. Thanks for the help!

For example: This table is related to tblOption by OptionGroupID
tblOptionGroup
OptionGroupID OptionGroup Name
1 YesNo
2 Chart Servings
3 Drinks
4 Food Resources

YesNo is used by Survey 1, 2 and 3. Drinks and Food Resources are options that can be used by Survey 2 and 3. Drinks and Food Resources also need to allow multiple selections. Chart Servings is used only by Survey 3.

I currently do not have the database design normalized for the surveys (I thought I had been thorough in the design process, but didn't realize the extra complications in designs for surveys.). Each respondent has a record of their responses to each survey. There is no data in the database yet, but I need to move quickly toward that end, so any help that can be given will be greatly appreciated!

 

[kbrown]

Thank you Pat, I don't know why that was such a block for me!

Now I am trying to figure out how to set up the tables and relationships for the six questions that can have multiple answers. I am planning on using a subform for data entry with a separate table to hold the responses: tblDrinks, tblFoodResources, tblAllergies, tblPica, tblVit_Meds, tblPMSymptoms. These are also many-to-many relationships. I think it makes sense to have each table set up like this, but I can also easily convince myself that it seems a little crazy:

tblDrinks
OptionID (primary key fld1, foreign key to tblOption)
ResponseID (primary key fld2, foreign key to tblResponses)

But then can I also have tblOption pkOptionID related to tblResponses fkOptionID? I need a way to relate the responses that can only have one selection. These responses can only have one option, so I think the relationship is one-to-many.

I am attaching a screenshot of what I think the relationships should look like. If you have time, can you take a look at this and let me know if there is a better way to handle this situation? Thank you

 

[kbrown]

I am working away incorporating these changes into my database design. I know I will have many questions during this process. I am currently working on redesigning my input forms. The examples I have seen all have each question on a separate page of the form so that you have to scroll through all the questions to enter them. What I would like to do is have all the questions on one form with space to respond to each of them. Is this possible? Do I need to have the question and then a subform for the response for each question? Attached is a copy of what I would like to approximate.

Thanks

 

[kbrown]

I think I've changed my mind about having all the questions on one form. I'm okay with data entry having each question on it's own form and just paging through the records to answer them all. Then, once the information has been entered, I'll use one form that will allow people to view all the responses to the survey.

I think I am getting there in making these changes, but for some reason I am unable to display the OptionID (response). Once I enter data and move to the next question, I cannot go back and view the response to the first question. Instead, I am being given the opportunity to add another response.

Data entry is set to No and I believe that the relationships are set up correctly. It looks like the data entered is in the table in the correct place.

I am attaching a copy of the database in 2K and 97, (I am using 2K). If you have time to look at it and see if something jumps out, I would appreciate it. In the meantime, I'll keep searching for the answer myself.

Thank you

 

[kbrown]

Okay, I am at a loss. I keep going around in circles with this. I am sure there is something I am just not interpreting correctly! I just keep coming up with more questions.

For instance, which table should have the date field in it? The examples I've seen all have it in the tblSurvey. In my database each person will take the survey multiple times, so the tblSurvey would end up with the survey name repeated for each date. It seems like that would make the table not follow normalization standards. Should there be a junction table for date? Between tblSurveys and tblSurveyQuestions or between tblClient and tblSurveyAnswers? I thought I understood normalization, but now I just think I am going crazy!

Any help is appreciated, thanks!

 

[kbrown]

Okay, I have been trying to work this out and this seems like a reasonable solution to me based on my current understanding and all the input I've been given. I would really appreciate feedback on the table structure, so that I will know whether or not I am headed in the right direction. Thank you!

Each client can have surveys done on many dates, but only one survey on a particular date.
Many surveys can be completed on the same date.
Each response should also be tied to the date.

Would this structure handle the situation?

tblScreenDate
ScreenDateID (autonumber primary key)
Date
Periodicity
ScreenID (foreign key to tblScreens)
ClientID (foreign key to tblClientInfo)

tblScreenAnswers
ScreenAnswerID (autonumber primary key)
ScreenQuestionID (foreign key to tblScreensQuestions)
ScreenDateID (foreign key to tblScreenDate)
ClientID (foreign key to tblClientInfo)
OptionID (foreign key to tblOption)
TextResponse - new field for entry of comments for the few questions that don't have a predefined response

Thank you!

 

[kbrown]

Thank you, Pat! I appreciate all of your help and the examples on table structure. I'm actually understanding it now!

 

[tinman]

I am currently trying to design a survey system in Access that allows a questionnaire system to be developed and then the data inputed and analysed. Ive split the various application into 3 phase operations:-

# Design & implementation (Design the questionnaire structure itself)
# Data Entry (Enter case data into a form interface)
# Analysis (Use queries to crosstabulate data and analyse various patterns
from the survey results)

The problems Im having relate to defining the types of responses, some questions have :-

List responses (Single reply)
Multi responses (Multiple tick reply)
Input (User entered data)

How can you conform to normalisation with :-


============ Design =======================
tblQuestions
QuestionID*
Question
Type >>>> Lookup 3 types (Input,Multi,List)

tblResponses
ResponseID*
Response

tblQuestionFormat
QuestionRef >>>> Lookup to Question from tblQuestions
Response >>> Lookup to Response from tblResponses

==============Data Input ==========================

tblCases
CaseID*
QuestionID**
Response >> Lookup to from qryFindMatchQuestionForResponse to find what response goes with the matching question
Validity (Type yes/no) - Whether the question was ticked
NoReply (Type yes/no) - Blank (Default is Yes)


The problem is when ever i try to inplement this into a relationship system, things go haywire, 1st things i need to know is:-


# Is access capable of this type of system
# How can this system be less complication
# Normalisation doesnt seem to work for these scenarios
# Does anyone know of an access sample database that is simular to this scenario.

Can anyone help because the more i try to develop this the complicated it gets and the ER diagram gets over complicated and i simply get lost. Has anyone ever managed to develop some like this in access, I've looked everywhere. The only other option is a commercial survey system like Snap/SPSS. We have SPSS but this software is too expensive to maintain and even use. Our data inputers have a real issue with SPSS.

Id rather design one that I can develop to my companies needs.


Other data:-

Each person can only do 1 questionnaire once only
Each person can skip various sections to filter out N/A questions

 

[kbrown]

It is possible to use Access for surveys, but it can be very complicated. There are a number of examples in the forum if you search with survey and questionnaire.

I am currently working on a database that has 3 surveys. Some questions can have only a single response from a drop down list, some can have multiple responses from a drop down list and some are user entered. Each person can fill out multiple surveys, but they must be on different dates with different periodicities. I have received a lot of help with normalizing my data and setting up relationships through this forum

I am attaching a copy of my relationships. My database is too big to attach, even with minimal data, but hopefully seeing the relationships will help you move forward. I'll try to get my database to a size that can be attached, but I probably won't be able to do it today.

 

[batman1056]

Surveys - I am having a few problems with mine. I`ll try and explain my layout and what I am trying to achive. I haev read numerious posts and am still having probs.

Layout:-
tblclient:- (holds all the clients details)
name
postcode etc..

tblprojects:- (list of all the projects - whcih are linked to questions)
project no. [key]
project start date
project end date

tlbquestions:- (list of questions that are specific to each project) y/n answer
project no. [link to tblprojects] 1 to many
Questiontext [what questions are to asked]
AnswerID [link to answers] [KEY]
QuestionOUT [my totals for this question for the yealy outputs i.e need 100]

tblanswer
AnswerID [link to tblquestions 1 to many]
QuestionAnswer y/n [yes/no answer only]

OK. I think I have this correct. still not 100%, but what I would like to do IF possible is to create a Form i.e. Questionare and on that show the persons name (or add a new one) and a TAB box, the tab box will have specific questions that are related to the tab ID. example. Tab 1 is marked P43, all the questions from tblquestions that have the project no. of 43 are in this section. Tab 2 is marked P10, and all the questions for tblquestions that have the project no. of 10 are in here only. The final Tab is for INFO, this will have a contact date, time and venue. this will enable me to work out how the person is linked to the outputs.

This will allow me to quickly go into a persons record and allocate them agaisnt a specific project, and a specific output (question).

If I can manage to create the links above and do what I want, how do I manage to do the tab fields.?

Any ideas on making this simple would be appreciated.