Survey Databases (1 Viewer)

[Mile-O]

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?

 

[The Stoat]

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

TS

 

[SamDeMan]

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

 

[SammyJ]

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.

 

[KenHigg]

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...

 

[Sergeant]

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.

 

[SammyJ]

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.

 

[KenHigg]

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

 

[Sergeant]

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.

 

[The Stoat]

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 ]. 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.

 

[SammyJ]

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.

 

[Sergeant]

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.

 

[KenHigg]

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...

???

 

[jsanders]

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.

 

[KenHigg]

See this thread / post:

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

 

[SammyJ]

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.

 

[Pat Hartman]

Ken, I don't understand requirement #7.

7. Application will run without having to get to the db objects.

 

[KenHigg]

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

 

[jsanders]

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.

 

[Liz A]

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?