I know it's a common question on this forum which, after having a search, we we have no definitive thread to refer others to. I was thinking I would pose the question, having had no experience in making such a database, so that the minds on this forum could offer their thoughts. When the discussion wanes I'm guessing it would be a great piece to read.

So, I suppose, the question is:

What considerations should one take when designing a survey database and how would one go about desiging the table structure?

Good first question. :)

I've done a couple and generally take this approach, though there are limitations due to the 255 field limit for tables i.e. if you have more than 255 questions i'd think twice about doing it this way.

I start with a questionnaire/survey table. If a question is yes/no or a comment field then it gets it's own field in the table.

If the field is multiple choice and only one answer is expected then i create a look-up of the possible answers and create a relationship between the Questionnaire table and the Look-up table with the PK of the look-up being stored in a field of the Questionnaire table.

If the question is multiple choice and more than 1 answer can be given i create a look-up table of the answers and an intermediary/link table to remove the many to many relationship between the look-up table and the Questionnaire table storing the Pk of the Questionnaire table in the link table with the PK of the look-up table.

The obvious point to make is the DB is only good for that specific questionnaire. As i don't do loads of questionnaires it's not really an issue however i imagine there is a way of creating a structure that would allow you to create questionnaires on the fly. Not sure i would want to work out what that would be though :D

TS

Ok, I don't think i will be able to give my full opinion on this, since it is friday and i am in a bit of a rush out of here. I will try to edit this post or add more in the upcoming days.

I worked on a survey and i must caution you that they are horrifying. If you build a table that has a column for each question and each answer can be answered with multiple answers, you will have a hard time with the crosstab queries. lets say tbl1 has 20 columns, 1 through 5 is about the person taking the survey and then 5 through 20 are the 15 questions. the possible answers can be: bad, not so bad, good, very good or excelent. you then want to summarize all your surveys and you will have a hard time. i am looking at my database now, i don't remember exactly all the different ways i tried, but it was tough.

there was one guy out there (Duane Hookom, Microsoft Access MVP), that directed me to his database. there you will find that he generates questions based on a taker. so that the form doesn't the simple structure of 10 questions.
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

good luck,

have a great weekend,

sam

I'd never do the above, unless I knew I was never using the database again, and never building a survey again.

I'd definitely have a questions table with one row per questions. It would need a primary key which could also serve to order the questions, or those could be independent.

Either way it would be also need a response type, being either text, or a lookup to a set of options.

One further table would house all sets of options, so I'd probably put two records in it initially for Yes and No, with the same OptionSetKey for example. They'd have a numeric value as well, so maybe 0 and 1 for those.

Another set would be Yes, No, Maybe. Then another would be Great, Good, OK, Bad, Terrible. You get the idea.

With these two table you should be able to structure a screen for most questions you could think of, and so you'd need one more table for responses.

That should do it.

To do a 'Pie in the Sky' survey db, wouldn't one first need to consider all the possible formats that the questions and their related responses would take?
Say 'Yes/No', 'Rate you answer 1-5', etc,...

Given that, I think maybe each type of question/response combination may best be considered from an object / class perspective...

Come to think of it, the responses may be the more complex side of the two...

OK, I work in Operational Test & Evaluation...this is a very welcome topic to me. We deal out surveys on a regular basis in order to gauge ease of use, etc...
I have toyed around with a db format for surveys, but thus far, we do it all manually.
My approach was:

tblSurveys:
SurveyID (PK)
SurveyTitle
TestID (FK to the Test tracking db)

tblQuestions:
SurveyID (FK)
QuestionID (PK)
QuestionText
ResponseOptionID (FK)

tblResponseOptions:
ResponseOptionID (PK)
ResponseText
ResponseValue

tblResponses:
ResponseID (PK)
QuestionID (FK)
ResponseValue
ResponseComment

(or something like that...off the top of my head)
I really look forward to seeing what people have to say about this.

Yep, that looks pretty much exactly how I'd be doing it.

Except that you will need more than one possible option for questions, so ResponseOptionID can't be a primary key of tblResponseOptions. ResponseValue would need to be part of the primary key.

So if I had a survey with 5 yse/no questions and 5 1-5 option questions, how would the table populations look?

Yep, that looks pretty much exactly how I'd be doing it.

Except that you will need more than one possible option for questions, so ResponseOptionID can't be a primary key of tblResponseOptions. ResponseValue would need to be part of the primary key.
Oh yeah. I think just make ResponseID and QuestionID a composite PK, no?

So if I had a survey with 5 yse/no questions and 5 1-5 option questions, how would the table populations look?
Using the RepsonseText and ResponseValue from tblResponseOptions, values of 1 & 2 can be programmed to mean true and false. As long as the respondant sees text that says 'true' or 'extremely satisfied', it doesn't matter how we capture that response, it will save as a value that matches up with that response text in the tblResponseOptions.

The idea was to discuss strategies, and it appears that we are...keep it coming.

Sarge.

Hi,

The "long and narrow" approach the Sergeant outlined is -i believe- the logical way if you are developing surveys regularly. It may even be the preferable way regardless. However with regards to "short and wide" way in which i have done mine i made use of pivot tables/graphs in Excel linked to queries in Access. They can really simplify the queries you need to do in order to get good reports so crosstab queries were really not an issue. They also allow you to make rapid changes to the format and structuring of the data you wish to display [which is great in presentations when someone asks you a question you didn't think of :D ]. The reason i point this out is if you use the "long and narrow" approach you'll need an equally flexible reporting tool as the questions and responses will obviously mean different things for each survey. Using the pivot method will allow you to quickly format a reporting package based on a simple set of underlying queries that should -by and large- be applicable for all your surveys.

Oh yeah. I think just make ResponseID and QuestionID a composite PK, no?

Actually I meant in tblResponseOptions make ResponseOptionID and ResponseValue the primary key.

The only thing really missing is a ResponseType field, which would determine how the options were treated.

It would apply to a single ResponseOptionID so would need to be in another table, which would have ResponseOptionID as a primary key.

Examples of values for it would be:

Text, indicating that the respondant would enter text, and there would not need to be entries in tblResponseOptions for this ResponseOptionID value.

Single, indicating that the respondant would choose one of the options presented. A form would use Option Buttons to retrieve an answer.

Multiple, indicating that the respondant could choose any of the options presented. A form would use Check Boxes to retrieve an answer, and would need to sum the values of those selected, and for these the ResponseValues would need to be 1,2,4,...

If the survey is not meant to be filled in on a computer, but needs to be printed, a left join to the tblResponseOptions table would make all of this fairly simple.

I wish I needed to survey some people now.

In the original whiteboard layout, I had named the response sets with a descriptive name...it is a good idea to list these in a separate table linked to ResponseOptions.
BTW, the ResponseValue column is numeric and two-fold: It orders the responses logically and identifies which textual option the respondant chose.

I would also add a ResponseGroupName to tblResponseOptions to link to tblResponseGroups, in lieu of a comp PK.

I'm having a hard time following this...

1. When I do a new survey, I may want to run the same survey more than once or maybe run the same survey for different groups.

2. So I would think that the system would need to allow the user to create a survey with it's questions, then when they get ready to actually record the responses, the system would create an instance of the survey...

???

I was thinking if you wanted a universal survey data base. You could have a main table and several daughter tables the daughters table could be questions with a certain answer format.

So the main table would contain survey descriptions.

Say table Question One is formatted to accommodate 1-10
Table Question Two is formatted standard multiple choice.
Etc, etc

When you’re creating the survey, you could have some flexibility in that if you need multiple choices you could create a new available answers drop down list that is filtered to a particular question. The idea of the different question tables is that you would have flexibility within one survey.

You could add to that a drop down list that had standard rating type answers or you could create a custom answer for each question.

I hope this made sense.

I’m typing on the fly.

See this thread / post:

http://www.access-programmers.co.uk/forums/showpost.php?p=456438&postcount=8

I was thinking if you wanted a universal survey data base. You could have a main table and several daughter tables the daughters table could be questions with a certain answer format.

So the main table would contain survey descriptions.

Say table Question One is formatted to accommodate 1-10
Table Question Two is formatted standard multiple choice.
Etc, etc

...

I hope this made sense.

I’m typing on the fly.

I can understand what you are describing, but it makes no sense to do it this way.

Ken, I don't understand requirement #7.
7. Application will run without having to get to the db objects.

Ken, I don't understand requirement #7.

Simply that the thing will run without the end user ever having the need see any of the behind the scene tables, queries, forms (in design view), etc. All they see is maybe a switchboard and the forms needed to perform all of the the routine operations... Kinda of a given, but I thought I'd throw it in ;)

I can understand what you are describing, but it makes no sense to do it this way.
I was nearly asleap when I wrote that.

I'll give it another try after diner.

Hi, I'm new here, but I think this is the appropriate forum for my questions. I am taking over the management of an Access database that was designed by someone else. Surveys are one part of the database and are currently organized in the non-normalized, "short and wide" approach (i.e., each question is a separate field). Originally the designer chose this route for ease of use by non-experts like me that would be using the database after he left (he was a consultant). However, I am now trying to decide whether I would be making things easier or more difficult for myself if I normalized the survey section of this database (one table for questions, one for responses, one with both, maybe another for survey type?). Before I go down this road, I'm wondering what people have to say about how easy it will be for me to build forms and queries from one approach vs. the other. I feel pretty adept at the non-normalized approach for building forms and queries, but am running into memory problems because I want to add specific field properties to each field (value lists, captions, etc.). I have no experience with building forms and queries with the normalized approach, plus people with even less experience will need to get data from this database regularly. So I am at a crossroads. Thoughts?

Liz, To me, a robust survey database application would be would be quite an undertaking for a newbie. But if you do commit to doing it the rewards could be substantial - You'd have you survey db and more important (to me anyway), would be your newly acquired Access / DB skills! I say go for it!

Thanks Ken, it is certainly a tough call. I would like to do this the "right" way, but might have to make it workable the "wrong" (i.e., flat) way first as there are several people who need to be using it now. Then if I can figure out the quirks of the more robust system, maybe I can replace with that. Or just learn it for when I set one of these up in the future. I appreciate the advice!

As I play around with this, I'm guessing I'll have more questions to post...

My favorite line here is - If you don't have time to do it right, what makes you think you have time to do it twice?

If you're going to do it wrong, you are far better off doing it with Excel. You will be very unhappy with how Access works with spreadsheetlike data. Once you see how it is done correctly with normalized tables, your palm will leave a dent in your forehead as you say duh!

Rather than making a completely generalized survey application, my suggestion would be to make a survey application for the current survey. Don't worry about expandability. Just worry about the current set of questions/answers. That will simplify your process and limit your exposure. Here is the simplest structure that makes sense. It still allows for future surveys as long as they are the same type.

tblSurveyTaker: (whoever takes the surveys)
SurveyTakerID (autonumber primary key)
FirstName
LastName
etc.
tblSurvey: (survey definition)
SurveyID (autonumber primary key)
SurveyName
etc.

tblSurveyQuestions: (questions for survey)
QuestionID (autonumber primary key)
SurveyID (foreign key to tblSurvey)
QuestionText

tblTakenSurveys:
SurveyTakerID (foreign key to tblSurveyTaker)
SurveyID (foreign key to tblSurvey)
QuestionID (foreign key to tblSurveyQuestions)
Answer

Thanks for the tip Pat! So, I've been attempting to create this database the way you pros have suggested and so far so good with setting up the tables, but beyond this, my lack of experience is getting the better of me. So here is my series of questions/concerns:

1. Is there any reason tblSurveyQuestions needs the foreign key to tblSurvey when tblTakenSurveys will connect these two? I have questions that appear in multiple surveys, but want to make sure this won't be a problem later.

2. I am adding another table, tblResponseOptions, as several questions share the same response choices. This table ends up being something like:

tblResponseChoices
ResponseID (autonumber primary key)
ResponseText
ResponseNumberCode (for later scoring)

and then the table that pulls everything together would be:

tblTakenSurveys
SurveyTakerID (autonumber primary key)
SurveyID (foreign key to tblSurvey)
QuestionID (foreign key to tblQuestions)
ResponseID (foreign key to tblResponseOptions)

Does all this make sense?

3. Now for a bigger, more involved questions. How do I turn all this into forms for data entry? I want to create forms that look like the surveys, but am having trouble wrapping my mind around this, since the questions are not fields themselves.

4. When I query this to get everything onto one sheet for analysis, should I be doing a select query to pull the relevant fields together, then a crosstab query where the questions become columns? Basically, I think I get the big picture conceptually, but I am getting lost in the details of how to make it happen.

I'd greatly appreciate any advice. Or perhaps there is a resource that explains all this to which someone might direct me?

Thanks,
Liz

1. Is there any reason tblSurveyQuestions needs the foreign key to tblSurvey when tblTakenSurveys will connect these two? I have questions that appear in multiple surveys, but want to make sure this won't be a problem later.In that case, you also have a many-to-many relationship between surveys and questions so the structure becomes:
tblSurveyTaker: (whoever takes the surveys)
SurveyTakerID (autonumber primary key)
FirstName
LastName
etc.

tblSurvey: (survey definition)
SurveyID (autonumber primary key)
SurveyName
etc.

tblQuestions: (individual questions)
QuestionID (autonumber primary key)
ResponseCodeSetID (foreign key to tblResponseCodeSets)
QuestionText

tblResponseCodeSets: (identifies a set of valid codes such as 1,2,..10 or a, b, c)
ResponseCodeSetID (autonumber primary key)
SetName

tblResponseCodes: (individual codes in a set - used to populate combo)
ResponseCodeID (autonumber primary key)
ResponseCode
ResponseMeaning

tblSurveyQuestions: (questions for survey)
SurveyQuestionID (autonumber primary key)
QuestionID (foreign key to tblQuestions, unique index fld1)
SurveyID (foreign key to tblSurvey, unique index fld2)

tblTakenSurveys:
SurveyTakerID (foreign key to tblSurveyTaker)
SurveyQuestionID (foreign key to tblSurveyQuestions)
ResponseCodeID (foreign key to tblResponseCodes)
2. tblTakenSurveys cannot contain both header and detail information. The first two fields occur only once per survey and there is very likely data associated with those two field. At a minimum, you probably want to keep the date the survey was taken. If the survey is administered by someone, you would want to log that person's ID, etc. That is why the four fields would end up in two tables.
3. Use a main form to capture the header information and a subform that lists all the questions for this particular survey. You can format the subform so that it sort of disappears into the main form if you want something that looks more like your paper form. However, if the subform contains more questions than will normally show, you will need to include scroll bars at a minimum.
4.Yes, much of the analysis will rely on crosstabs.

tblQuestions: (individual questions)
QuestionID (autonumber primary key)
ResponseCodeSetID (foreign key to tblResponseCodeSets)
QuestionText

tblResponseCodeSets: (identifies a set of valid codes such as 1,2,..10 or a, b, c)
ResponseCodeSetID (autonumber primary key)
SetName

tblResponseCodes: (individual codes in a set - used to populate combo)
ResponseCodeID (autonumber primary key)
ResponseCode
ResponseMeaning



OK, I'm starting to get this. Now maybe I'm missing something, but why would ResponseCodeSetID be a foreign key in tblQuestions and not tblResponseCodes?

Also, I should mention that these surveys are part of a larger database, which is why I did not include a table with the "survey taker's" identifying info in my previous post. This table already exists and these survey tables will be linked to it through tblTakenSurveys.

And lastly, is there a good resource that explains step-by-step how to create entry forms from this type of table structure? I am not seeing quite how the combos will be populated, nor how the entered data will get into the right place. I'm going to play around with it a bit more to try to figure it out, but I think I may need some more explicit guidance...

-Liz

ResponseCodeSetID is a foreign key in tblQuestions. This identifies which set of responses is valid for a question. Having a set id allows you to have multiple ranges such as 1-10, 1-5, a-d, etc.

ResponseCodeID is a foreign key in tblTakenSurveys. It identifies which value of a set was chosen so it represents 1, 8, c, etc.

Right, I understand what ResponseCodeSetID refers to, but am confused as to how it is actually used. I would have thought one would want to relate ResponseCodeSetID back to the actual response codes in tblResponseCodes. How am I to be using this exactly? I mean, where does this information as to ranges of valid responses get implemented for each question? Maybe there is an example of this somewhere?

Also, for this table structure, must all the joins be inner joins? I had them as left outer joins and was getting that ambiguous join error when I tried to run a query, but it worked when I made everything an inner(equi) join.

Along the lines of table relationships, is there any reason to use or not use lookup functions in tables to enter the foreign keys?

As for the entry forms issue, I have searched these forums plus the two Access books I have (Access Bible and Access Database Design & Programming) but am still not quite getting how to design the forms. Am I just missing something? I realize I still have a lot to learn...

Thanks once again,
Liz

I would have thought one would want to relate ResponseCodeSetID back to the actual response codes in tblResponseCodes.You relate the "set" of response codes to the question definition to define what is valid. This set will be used to populate the combo on the form. You relate the specific response code to the responder's answer. You can infer the set from the specific response code but you shouldn't need to.
must all the joins be inner joins?For this application, I would use only inner joins. The only place you might want a left join is if you want to find responders who have not actually taken a query.
Along the lines of table relationships, is there any reason to use or not use lookup functions in tables to enter the foreign keys?I NEVER use the lookup "feature". I find that it causes more problems than it solves. It will do nothing but cause you confusion if you use queries or VBA.

The form will be a main form where you match the responder with a particular survey. I would add a button to this form and in its click event, I would run an append query that adds empty answer records to populate the subform. That means that you must disable the Add and Delete options for that subform. I suggest a button because it is easier to control when the answer records get added. Pressing the button a second time should result in an error or you can simply ignore the error because as long as your primary key and unique indexes are properly defined, Jet will prevent duplicate questions from being added.

So, I'm still working on this one and could use some more help... I've set up the table structure according to the above, and am working on the forms, but am getting a bit stuck. As per what you wrote:

The form will be a main form where you match the responder with a particular survey. I would add a button to this form and in its click event, I would run an append query that adds empty answer records to populate the subform. That means that you must disable the Add and Delete options for that subform. I suggest a button because it is easier to control when the answer records get added. Pressing the button a second time should result in an error or you can simply ignore the error because as long as your primary key and unique indexes are properly defined, Jet will prevent duplicate questions from being added.

Any chance you (or someone) could explain in a bit more detail how and what needs to be in this append query? And how to run the query from a button? I am going to play around with it, but I don't really know code (or if that's necessary). I finally figured out how to make the subform that pulls the question text from the tblQuestions and enters responses via a combo bound to tblTakenSurveys.ResponseCodeID. But now, like you said, I need to populate this (continuous) subform for each survey taker. I was thinking I would basically need to be adding new records to the tblTakenSurveys table but because this table requires an entry in the foreign key ResponseCodeID, I can't leave that field empty. Should I create a response code that would have the meaning "not yet entered" or is there a cleaner way? Or am I missing something?

(At this point, I'm wondering if this thread should be moved to Forms, so feel free to re-direct there if appropriate.)

Thanks again,
Liz

OK, so I am trying to create an append query to populate the subform, but it is not working. I think this is because I need to pull autonumber PK fields from other tables to append to tblTakenSurveys (all the fields in this table, other than the PK, are foreign keys). In looking into this, it seems I cannot use autonumber fields in an append query, but if this is true, then I cannot append new records to tblTakenSurveys. I read somewhere else on this forum that this can be done through creating a temporary table and appending that, so I am going to try this next, but a) is this the right move, and b) if so, how does one create a "temporary" table? Do I delete the whole table when I'm done or is there a way to create a table that "knows" it is temporary?

Figured it out! No temporary table necessary, and I guess it was exactly what Pat was telling me to do, but I didn't get it. In case anyone is interested, here is where I reposted this question in more detail, then posted how I resolved it:

http://access-programmers.co.uk/forums/showthread.php?t=102971

It always seems to be something "simple", yet the answers are surprisingly difficult to find...

I haven't looked thru all the posts but I like the overall structure Sergeant recommended (link (http://www.access-programmers.co.uk/forums/showpost.php?p=455312&postcount=6)). I'm working on a survey database right now and this is my current setup:


Employee
--------
EID (PK)
FirstName
LastName
etc.

Question
--------
QID (PK; Auto)
QuestionText (Memo)

Configuration
-------------
CID (PK; Auto)
QuestionOrder (Number)
QuestionID (FK)

Multiple Choice Key
-------------------
MCKID (PK; Auto)
QuestionID (FK)
ChoiceText (Memo)

Multiple Choice Response
------------------------
EmployeeID (FK)
DateAnswered
ConfigurationID (FK)
MultipleChoiceKeyID (FK)

Free Response
-------------
EmployeeID (FK)
DateAnswered
ConfigurationID (FK)
QuestionID (FK)
Response (Memo)

(These other tables are less typical and most people wouldn't need them)

Subquestion
-----------
QuestionID (FK)
SubquestionID (FK)

QuestionHeading
---------------
HeadingID (FK)
QuestionID (FK)

Heading
-------
HID (PK; Auto)
HeadingText (Memo)

The one thing I'm definitely going to change is how my multiple choice keys link to the questions. It's better if the keys are grouped in to "keysets" and each question links to a specific keyset. So two questions can have the same set of choices (without duplicating the choices in the MultipleChoiceKey table).