A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Survey Database Table Design



 
 
Thread Tools Display Modes
  #1  
Old January 19th, 2010, 11:24 PM posted to microsoft.public.access.gettingstarted
Tara Metzger
external usenet poster
 
Posts: 19
Default 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  
Old January 20th, 2010, 01:37 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old January 20th, 2010, 07:23 AM posted to microsoft.public.access.gettingstarted
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old January 20th, 2010, 10:06 AM posted to microsoft.public.access.gettingstarted
Keith Wilby
external usenet poster
 
Posts: 812
Default 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  
Old January 20th, 2010, 05:24 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default 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  
Old January 20th, 2010, 05:41 PM posted to microsoft.public.access.gettingstarted
Lynn Trapp[_3_]
external usenet poster
 
Posts: 101
Default 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  
Old January 20th, 2010, 07:14 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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  
Old January 20th, 2010, 09:40 PM posted to microsoft.public.access.gettingstarted
Tara Metzger
external usenet poster
 
Posts: 19
Default 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



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  
Old January 20th, 2010, 11:32 PM posted to microsoft.public.access.gettingstarted
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old February 8th, 2010, 10:31 PM posted to microsoft.public.access.gettingstarted
Tara Metzger
external usenet poster
 
Posts: 19
Default 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
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...file-cach.aspx
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.