If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Survey Database Table Design
Hello,
I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and all the possible answers, respectively. Would you recommend a 3rd table which ties all the responses from the tblQuestions & tblAnswers together? For some reason I'm stumped by this and I'm sure it's a very easy solution. Thank you! Submitted via EggHeadCafe - Software Developer Portal of Choice Design Patterns for .NET http://www.eggheadcafe.com/tutorials...s-for-net.aspx |
#2
|
|||
|
|||
Survey Database Table Design
On Tue, 19 Jan 2010 14:24:48 -0800, Tara Metzger wrote:
Hello, I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and all the possible answers, respectively. Would you recommend a 3rd table which ties all the responses from the tblQuestions & tblAnswers together? For some reason I'm stumped by this and I'm sure it's a very easy solution. Thank you! You need at least a couple more tables: let's call them tblSurvey (one record for each time a survey is filled out) and tblResponses (related one to many to tblSurvey, who took the survey and when, and to tblQuestions, which question is being answered). For a fully worked out example see Duane Hookum's "At Your Survey": http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000' or Roger Carlson's Training Registration database: http://www.rogersaccesslibrary.com/d...gistration.mdb -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Survey Database Table Design
Mr. Juhn Vunsun,
There are three Os in Hookom ;-) -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Tue, 19 Jan 2010 14:24:48 -0800, Tara Metzger wrote: Hello, I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and all the possible answers, respectively. Would you recommend a 3rd table which ties all the responses from the tblQuestions & tblAnswers together? For some reason I'm stumped by this and I'm sure it's a very easy solution. Thank you! You need at least a couple more tables: let's call them tblSurvey (one record for each time a survey is filled out) and tblResponses (related one to many to tblSurvey, who took the survey and when, and to tblQuestions, which question is being answered). For a fully worked out example see Duane Hookum's "At Your Survey": http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000' or Roger Carlson's Training Registration database: http://www.rogersaccesslibrary.com/d...gistration.mdb -- John W. Vinson [MVP] . |
#4
|
|||
|
|||
Survey Database Table Design
Tara Metzger wrote in message ...
Hello, I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and all the possible answers, respectively. Would you recommend a 3rd table which ties all the responses from the tblQuestions & tblAnswers together? For some reason I'm stumped by this and I'm sure it's a very easy solution. Thank you! I have a survey database that works well. Tables a tblQuestions - lookup table. Contains one question per record. tblUsers - contains one record per user. Stores employee number and network logon ID. Linked 1:M to tblSurveyTaken tblSurveyTaken - contains one record per user survey taken. Stores data such as Survey Date and Time, Department Number and User Location. Linked M:1 to tblUsers (each user can take many surveys). Linked 1:M to tblAnswers. tblAnswers - contains a copy of the questions in tblQuestions for each survey taken plus the user's responses. Linked M:1 to tblSurveyTaken (each survey contains many answers). I like this approach because a copy of the questions is taken with every survey, so if the questions ever need to change then existing question/answer combinations will not be affected. Keith. www.keithwilby.co.uk |
#5
|
|||
|
|||
Survey Database Table Design
OH!!!!
or perhaps Oh, Oh, OH!!!! But perhaps we John should respond "Uh-Oh" John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Duane Hookom wrote: Mr. Juhn Vunsun, There are three Os in Hookom ;-) |
#6
|
|||
|
|||
Survey Database Table Design
I think "Hookem" sounds better -- as in Hook Em Horns!!!!
-- Lynn Trapp MCP, MOS, MCAS "Duane Hookom" wrote: Mr. Juhn Vunsun, There are three Os in Hookom ;-) -- Duane Hookom Microsoft Access MVP |
#7
|
|||
|
|||
Survey Database Table Design
On Tue, 19 Jan 2010 22:23:01 -0800, Duane Hookom
wrote: Mr. Juhn Vunsun, There are three Os in Hookom ;-) blush running off to fix my boilerplate with the correct spelling and the current link -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Db Tables
Thank you for responding to my question. I not only appreciate it but I do have more tables than what was originally posted. Didn't want to bore you with details. However, now that you said more tables are needed, let me show you what I have.
Looking at all the tables you need to know this db contains 3 separate surveys that all need to be tied together via the Reservation #. Each Reservation # will complete a max of 2 surveys. PII will be completed by all and the other two will depend upon who was performing the work. All surveys are subject to change over time (ask different questions) and analysis needs to be performed for each of the 3 surveys. tblAddreses: ID-pk Number Street City State Zip tblReservation: ReservationNo-pk ContactLast ContactFirst CompanyName ComplexName ComplexAddressID ContactPhone ContractorID ContactID tblAnswers: ID-pk QuestID QValue Answer SpecialID tblSpecialInfo: ID-pk SpecialInfo tblSurveyList: ID-pk SurveyName tblContactPerson: ID-pk ContractorID FirstName LastName Title AddressID Phone Fax tblContractor: ID-pk SCNo CoName LicenseNo AddressID Phone Fax Website tblQuestions: ID-pk QuestNo Question AnswerID SurveyID tblInHouseSurveyResults: ID-pk ReservationNo Lists all the responses to the survey tblPIISurveyResults: ID-pk ReservationNo Lists all the responses to the survey tblContractorSurveyResults: ID-pk ReservationNo Lists all the responses to the survey Thank you, Tara John W. Vinson wrote: You need at least a couple more tables: let us call them tblSurvey (one 19-Jan-10 You need at least a couple more tables: let us call them tblSurvey (one record for each time a survey is filled out) and tblResponses (related one to many to tblSurvey, who took the survey and when, and to tblQuestions, which question is being answered). For a fully worked out example see Duane Hookum's "At Your Survey": http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000' or Roger Carlson's Training Registration database: http://www.rogersaccesslibrary.com/d...gistration.mdb -- John W. Vinson [MVP] Previous Posts In This Thread: Submitted via EggHeadCafe - Software Developer Portal of Choice Using ASP.NET User Controls http://www.eggheadcafe.com/tutorials...er-contro.aspx |
#9
|
|||
|
|||
Survey Database Table Design
Duane, that's uncharacteristically uncharitable of you to chide John for his
misspelling of Hokum. g, d & r -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Duane Hookom" wrote in message ... Mr. Juhn Vunsun, There are three Os in Hookom ;-) -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Tue, 19 Jan 2010 14:24:48 -0800, Tara Metzger wrote: Hello, I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and all the possible answers, respectively. Would you recommend a 3rd table which ties all the responses from the tblQuestions & tblAnswers together? For some reason I'm stumped by this and I'm sure it's a very easy solution. Thank you! You need at least a couple more tables: let's call them tblSurvey (one record for each time a survey is filled out) and tblResponses (related one to many to tblSurvey, who took the survey and when, and to tblQuestions, which question is being answered). For a fully worked out example see Duane Hookum's "At Your Survey": http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000' or Roger Carlson's Training Registration database: http://www.rogersaccesslibrary.com/d...gistration.mdb -- John W. Vinson [MVP] . |
#10
|
|||
|
|||
Keith, Your information was just what I needed.
Keith, Your information was just what I needed. Do you include an actual copy of the questions or do you reference the QuestionID? I currently have it set up to use the QuestionID however, that doesn't give you an actual copy of the questions.
Keith Wilby wrote: I have a survey database that works well. 20-Jan-10 I have a survey database that works well. Tables a tblQuestions - lookup table. Contains one question per record. tblUsers - contains one record per user. Stores employee number and network logon ID. Linked 1:M to tblSurveyTaken tblSurveyTaken - contains one record per user survey taken. Stores data such as Survey Date and Time, Department Number and User Location. Linked M:1 to tblUsers (each user can take many surveys). Linked 1:M to tblAnswers. tblAnswers - contains a copy of the questions in tblQuestions for each survey taken plus the user's responses. Linked M:1 to tblSurveyTaken (each survey contains many answers). I like this approach because a copy of the questions is taken with every survey, so if the questions ever need to change then existing question/answer combinations will not be affected. Keith. www.keithwilby.co.uk Previous Posts In This Thread: On Tuesday, January 19, 2010 5:24 PM Tara Metzger wrote: Survey Database Table Design Hello, I'm creating a survey database (Access 07). I have multiple tables and was wondering if you could help me with the design. Currently I have a tblQuestions & tblAnswers which contain only the questions themselves and all the possible answers, respectively. Would you recommend a 3rd table which ties all the responses from the tblQuestions & tblAnswers together? For some reason I'm stumped by this and I'm sure it's a very easy solution. Thank you! On Tuesday, January 19, 2010 7:37 PM John W. Vinson wrote: You need at least a couple more tables: let us call them tblSurvey (one You need at least a couple more tables: let us call them tblSurvey (one record for each time a survey is filled out) and tblResponses (related one to many to tblSurvey, who took the survey and when, and to tblQuestions, which question is being answered). For a fully worked out example see Duane Hookum's "At Your Survey": http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At%20Your%20Survey%2 02000' or Roger Carlson's Training Registration database: http://www.rogersaccesslibrary.com/d...gistration.mdb -- John W. Vinson [MVP] On Wednesday, January 20, 2010 1:23 AM Duane Hookom wrote: Mr. Mr. Juhn Vunsun, There are three Os in Hookom ;-) -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Wednesday, January 20, 2010 4:06 AM Keith Wilby wrote: I have a survey database that works well. I have a survey database that works well. Tables a tblQuestions - lookup table. Contains one question per record. tblUsers - contains one record per user. Stores employee number and network logon ID. Linked 1:M to tblSurveyTaken tblSurveyTaken - contains one record per user survey taken. Stores data such as Survey Date and Time, Department Number and User Location. Linked M:1 to tblUsers (each user can take many surveys). Linked 1:M to tblAnswers. tblAnswers - contains a copy of the questions in tblQuestions for each survey taken plus the user's responses. Linked M:1 to tblSurveyTaken (each survey contains many answers). I like this approach because a copy of the questions is taken with every survey, so if the questions ever need to change then existing question/answer combinations will not be affected. Keith. www.keithwilby.co.uk On Wednesday, January 20, 2010 11:24 AM John Spencer wrote: OH!!!!or perhapsOh, Oh, OH!!!! OH!!!! or perhaps Oh, Oh, OH!!!! But perhaps we John should respond "Uh-Oh" John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Duane Hookom wrote: On Wednesday, January 20, 2010 11:41 AM Lynn Trapp wrote: I think "Hookem" sounds better -- as in Hook Em Horns!!!! I think "Hookem" sounds better -- as in Hook Em Horns!!!! -- Lynn Trapp MCP, MOS, MCAS "Duane Hookom" wrote: On Wednesday, January 20, 2010 1:14 PM John W. Vinson wrote: blushrunning off to fix my boilerplate with the correct spelling and the blush running off to fix my boilerplate with the correct spelling and the current link -- John W. Vinson [MVP] On Wednesday, January 20, 2010 3:40 PM Tara Metzger wrote: Db Tables Thank you for responding to my question. I not only appreciate it but I do have more tables than what was originally posted. Didn't want to bore you with details. However, now that you said more tables are needed, let me show you what I have. Looking at all the tables you need to know this db contains 3 separate surveys that all need to be tied together via the Reservation #. Each Reservation # will complete a max of 2 surveys. PII will be completed by all and the other two will depend upon who was performing the work. All surveys are subject to change over time (ask different questions) and analysis needs to be performed for each of the 3 surveys. tblAddreses: ID-pk Number Street City State Zip tblReservation: ReservationNo-pk ContactLast ContactFirst CompanyName ComplexName ComplexAddressID ContactPhone ContractorID ContactID tblAnswers: ID-pk QuestID QValue Answer SpecialID tblSpecialInfo: ID-pk SpecialInfo tblSurveyList: ID-pk SurveyName tblContactPerson: ID-pk ContractorID FirstName LastName Title AddressID Phone Fax tblContractor: ID-pk SCNo CoName LicenseNo AddressID Phone Fax Website tblQuestions: ID-pk QuestNo Question AnswerID SurveyID tblInHouseSurveyResults: ID-pk ReservationNo Lists all the responses to the survey tblPIISurveyResults: ID-pk ReservationNo Lists all the responses to the survey tblContractorSurveyResults: ID-pk ReservationNo Lists all the responses to the survey Thank you, Tara On Wednesday, January 20, 2010 5:32 PM Douglas J. Steele wrote: Duane, that is uncharacteristically uncharitable of you to chide John for Duane, that is uncharacteristically uncharitable of you to chide John for his misspelling of Hokum. g, d & r -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) Submitted via EggHeadCafe - Software Developer Portal of Choice A Memory-Mapped File Cache TCpListener Service http://www.eggheadcafe.com/tutorials...file-cach.aspx |
Thread Tools | |
Display Modes | |
|
|