Keith, Your information was just what I needed.
in [Getting Started]
|
From: Tara on 8 Feb 2010 16:31 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 are: 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%202000' or Roger Carlson's Training Registration database: http://www.rogersaccesslibrary.com/download3.asp?SampleName=TrainingRegistration.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 are: 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: <blush><running 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/aspnet/1f1db3c2-c435-452a-83e0-d39aff2c9bb3/a-memorymapped-file-cach.aspx
|
Pages: 1 Prev: Datasheet Form Next: Datasheet Form - little stevie is pimping AGAIN |