Not quite sure what your question is... whether or not you should use a comma delimited list of items or a separate record for each? If that is your question.... most definitely a separate record for each question AND answer
Here's a script I use for building the necessary tables for a survey app I did... It can house multiple surveys, multiple questions per survey, and multiple answers per question. At the 'question' level, you define the form field type to use for that particular question. Eg... radio (single answer), checkbox (multiple answers), text, textarea... The backend has everything you'd need... including reports and it all works great with this database schema. There is only one type of answer that I haven't put in it and that is a question with multiple answers but each of those answers would have multiple answers themselves... It would be used for rating each answer to one question. Something like... Rate These Answers: One [bad] [good] [great] Two [bad] [good] [great] Three [bad] [good] [great] Email me offlist if you'd like to see the app and its administrative tools in action. ========== srv_globals.cfm ================================= <cfscript> request.surveydsn = "surveyapp"; request.tblprefix = "tbl_survey_"; request.debugon = "yes"; request.imageroot = "http://#cgi.server_name#/images";; request.webroot = "http://#cgi.server_name#/surveys";; </cfscript> =========== msaccess_tbl_script.cfm ========================== <cfinclude template="surveys/srv_globals.cfm"> <cfoutput> <cftry> <cfquery datasource="#request.surveydsn#"> create table #request.tblprefix#as ( answerid counter primary key , questionid number , surveyid number , answer text(250) , fieldtype text(50) , sortid number ) </cfquery> <div>#request.tblprefix#as was created successfully</div> <cfcatch type="any"> <div style="color:##ff0000;">#request.tblprefix#as already exists</div> </cfcatch> </cftry> <cftry> <cfquery datasource="#request.surveydsn#"> create table #request.tblprefix#qs ( questionid counter primary key , surveyid number , question memo , fieldtype text(50) , sortid number ) </cfquery> <div>#request.tblprefix#qs was created successfully</div> <cfcatch type="any"> <div style="color:##ff0000;">#request.tblprefix#qs already exists</div> </cfcatch> </cftry> <cftry> <cfquery datasource="#request.surveydsn#"> create table #request.tblprefix#faqs ( faqid counter primary key , faqquestion memo , faqanswer memo , sortid number ) </cfquery> <div>#request.tblprefix#faqs was created successfully</div> <cfcatch type="any"> <div style="color:##ff0000;">#request.tblprefix#faqs already exists</div> </cfcatch> </cftry> <cftry> <cfquery datasource="#request.surveydsn#"> create table #request.tblprefix#results ( resultid counter primary key , submitid number , surveyid number , questionid number , answer memo ) </cfquery> <div>#request.tblprefix#results was created successfully</div> <cfcatch type="any"> <div style="color:##ff0000;">#request.tblprefix#results already exists</div> </cfcatch> </cftry> <cftry> <cfquery datasource="#request.surveydsn#"> create table #request.tblprefix#submits ( submitid counter primary key , surveyid number , dateadded datetime , fname text(20) , lname text(20) , address text(150) , address2 text(150) , city text(50) , state text(20) , zip text(15) , comments memo , emailaddr text(150) , contactme number ) </cfquery> <div>#request.tblprefix#submits was created successfully</div> <cfcatch type="any"> <div style="color:##ff0000;">#request.tblprefix#submits already exists</div> </cfcatch> </cftry> <cftry> <cfquery datasource="#request.surveydsn#"> create table #request.tblprefix#surveys ( surveyid counter primary key , surveyname text(250) , activesurvey number , dateadded datetime , confirmation memo ) </cfquery> <div>#request.tblprefix#surveys was created successfully</div> <cfcatch type="any"> <div style="color:##ff0000;">#request.tblprefix#surveys already exists</div> </cfcatch> </cftry> </cfoutput> ..:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:228120 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
