From: Tara on
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
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

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