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:
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!
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 Your Survey 2000'
or
Roger Carlson's Training Registration database:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=TrainingRegistration.mdb
--
John W. Vinson [MVP]
Mr.
Mr. Juhn Vunsun,
There are three Os in Hookom ;-)
--
Duane Hookom
Microsoft Access MVP
:
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
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:
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
:
<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]
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
Email
tblContractor:
ID-pk
SCNo
CoName
LicenseNo
AddressID
Phone
Fax
Email
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
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
(no private e-mails, please)
Submitted via EggHeadCafe - Software Developer Portal of Choice
A Memory-Mapped File Cache TCpListener Service
http://www.eggheadcafe.com/tutorial...e0-d39aff2c9bb3/a-memorymapped-file-cach.aspx