Best way to develop db for survey?

[maxtrixx]

what is the best way to develop a database for a survey? How should I name the columns in the database? I have an survey to develop and some of the questions have up to 80 answer options + quantity for the options. For example, I have a list of truck models from different manufacturers (eg: John Deere[304, 444, 544, 624...], NewHolland[LW50, LW110, LW130...]. The person filling out the survey needs to be able to select from none to all of items and this would only be question #1. That means if the person filling out the survey select JD 304 and NH LW110 I'll have a total of four entries one for each of the trucks and one of quantity for each of the trucks. If I use a column for each possible answer, I'll end up with about 1000 columns what to me wouldn't be a very well developed database. Anyone has done anything similar? Any ideas? For now this has to be done in Access.

[bentkey]

Assuming you are not keeping any information about the person taking the survey, it sounds like a 3 table relational database. The first column would be your survey questions. The 2nd table would be your possible answers to all questions. Your 3rd table would only hold a field relating to the primary key of your question table and a field relating to the primary key of your answer table and a quantity field. I would probably give it a primary key also, because I always do. Make these primary key fields auto generated. When a person makes a selection, a new record is added that links the question to the answer along with quantity. You add a lot of very small records, but that's much more efficient than having huge records. And I'd find something other than Access if you plan to have very many people take the survey.

[maxtrixx]

Thanks for the reply.

I am keeping information about the person keeping the survey because they are our customers. Also, half of the questions in the survey are open ended question, and the person being surveyed can answer pretty much anything.

Would this work?
Survey(SID, SName) Do I need this table? The way it's set up now three surveys are using the same database. (I inherited the project)
Question(QID, QuestionText)
Suvery_Question(SID, QID)
Answer(AID, PossibleAnswer)
Question_Answer(QID, AID, Answer) This table would store the actual answers

[bentkey]

I think you would need an AID field in the Survey_question table to link possible answers to survey questions and an SID field in the Question_Answer table to relate your answers to surveys. If the surveys are in similar format then I don't see any problem storing them in the same db and using SIDs to sort them out. You may be able to reuse questions and the same front end application could work with all of them. The tables you have listed seem to be atomic enough so that if you need to massage something later I don't think it will be too much trouble.

[maxtrixx]

Ok, now suppose I want to update one of the answers, how would I do that? Right now I can select all answers and questions and put them on a table but let's say I would like to update an answer that I posted for question 22b for survey 1. I added the field UserName to the question_answer table so I can do a search by UserName, but how do I select a specific answer? Thanks again.

[bentkey]

Ah yes, you should also have a primary key for your answer table to retrieve and update any particular answer (this is why I put a numeric primary key field in every single table I create whether I think I need it or not). Then do SQL select on the SID,QID, and from the retrived result pick the answer you want to update and use it's ,"QAID?" in the SQL Update where clause. Personally, I would not implement a way to change a survey result once entered, just because it is a survey. I would probably store the information the user was entering, and have them confirm that is really what they want and then commit the transaction and that's it. If you must be able to retrieve a particular respondents answers, then another identifier is needed, perhaps the name of the respondent in the answer table. This would make it possible to retrieve all answers to Survey "silly survey" to question "silly question" where respondent's name is "serious man" pick one, and update it. Of course if you let "serious man" take the survey twice things get messy so you would then need to make the survey taker name field unique in another table and then enter it as a foreign key relation/constraint in the Question_answer table. Hope that was clearer than mud. ;) Just curious, why would you want to do this?

[maxtrixx]

The deal is, this surveys are filled out by field reps, they talk to people that use our products and fill out a survey off line and once they are in the network all they have to do is submit the survey. Very often, when some of these reps submit their surveys they want to update some of the information later on, let's say they talked with the customer again and they got more information or they accidentaly submitted an incomplete survey. Right now half of the app is built, like I said I inherited the project. Anyways, I think I got it to work now, I can retrieve surveys and everything right now I'm just trying to find a way to select a specific survey from a list, would you have any ideas?? Thanks for your help.

[maxtrixx]

Oops, I guess you've already answered my question. I'm adding the respondent to the answers table. Thanks again!

[maxtrixx]

Another problem. How do I update the survey now? The items in the original form are named in the following format QID_## where ## = QuestioID in the database. So I insert the ## frin QID_## with the record in the QuestionID column. There's an autonumber for AnswerID. When I select a survey, multiple records show up and I don't know if I can use <%response.write(rs("AnswerID"))%> it's not only one for the selected survey. I

Example:
Answers table(it has more items but these two are the ones I need for the update)
QuestionID Answer AnswerID SurveyID
113 TruckA 2356 3
114 Bucket 2357 3
115 .... 2358 3
.... .... 2359 3

Basically what I need to know is how to put "Bucket" in the textbox QID_114.

[bentkey]

If I understand you correctly, the answers table has an automatically generated ID when a respondent fills in an answer, along with their name as we discussed before. So, to update you would first need to do a select and retrieve the answers he entered and them do an update on the one you select. So the basic select sql would look something like this.
"select * from answers where sid = 'your surveyid' and qid = 'your questonid' and resname = 'your respname' " this should return all answers to a particular survey to a particular question by a particular respondent. When you select the record you wish to update, it will also have a unique key number which you automatically generated. So you would store and use that number in your update statement. Something like this:
"update answers set answer = 'Bucket' where aid = 'your stored aid' " and this should only affect one possible record.
Did this answer your question, or did I misunderstand completely?

[maxtrixx]

Sorry, I don't think I explained it right. The update isn't the problem here, what is getting in the way is putting the infomation that's in the database in an update form. If I don't bring it all back the user would have to fill out the entire survey again since all form fields are going to be blank. I can't use the column name and I need to user the row to call that answer. Any ideas?

[bentkey]

You should just have to present the survey similar to a user taking it for the first time, and execute a select sql statement similar to the one I wrote in my last response for each question, to retrieve that respondents answer to each question.

[maxtrixx]

Any ideas why this is not working?

SQL = "UPDATE Answers SET Answer = '" & Replace(FieldValue, "'", "''") & "', UserName = '" & UserName & "', SurveyDate = '" & SurveyDate & "', Respondent = '" & Respondent & "', CompanyName = '" & CompanyName & "' WHERE QuestionID = " & QuestionID & " AND IntervieweeID = " & ID & " "

This is what it prints:

UPDATE Answers SET Answer = 'Answer Test', UserName = 'John Doe', SurveyDate = '08/03/2004', Respondent = 'Respondent Name', CompanyName = 'Company Name' WHERE QuestionID = 1705 AND IntervieweeID = 5

I still haven't been able to get the answer to show up. This works if I leave only the Aswer field to be updated.

[bentkey]

Regretably I cannot help with the script syntax you are using, because I am not familiar with it. I would still add the survey ID to the where clause in case this respondent should take more than one survey.

[maxtrixx]

Regretably I cannot help with the script syntax you are using, because I am not familiar with it. I would still add the survey ID to the where clause in case this respondent should take more than one survey.

When you inherit a project you're sometimes stuck with what you got. The last guys used the IntervieweeID as the identifier for the a specific survey. There was 3 surveys in the same db so now I'm splitting them up becuase access will not support al the info from all surveys. SurveyID was the identifier for the 3 different surveys.

[OMGALD]

Save yourself the trouble. Try out http://www.survsoft.com