Linking Many Surveys to one participant number

[Metalmaniac]

Hi there,

I am creating a database for the entry of 4 different questionnaires for a
number of different participants. Each participant number is unique and they
are not entered in order. Each participant will fill out all 4 questionnaires
only once. I have constructed the tables to hold the data for each of the
questionnaires and a table for participant numbers and the date of entry. I
would like to be able to link the tables such that I can see the 4
questionnaires associated with each participant number in the one table (much
like seeing a clients' order history by linking a client information table
and a purchases table). The number of questionnaires is likely to increase
dramatically in the future so putting all of the information on one table
creates too many indexes and cannot be saved. I have also tried to set a
one-to-one relationship but that will only display the date of entry under
the "plus" menu on the table for a specific questionnaire. Any help on this
matter would be greatly appreciated. Thanks.

 

[tina]

from your limited explanation, i'm thinking you may have some issues with
tables/relationships design. please post your tables' structures. as an
example:

tblA
Field1 (primary key)
Field2
Field3

tblB
Field1 (pk)
Field2 (foreign key from tblA)
Field3

hth

 

[Steve]

Consider the following tables ...........
TblParticipant
ParticipantID
<fields about each participant you need>

TblSurvey
SurveyID
SurveyDate
<fields about each survey you need>

TblSurveyQuestion
SurveyQiestionID
SurveyID
SurveyQuestionNumber
SurveyQuestion

TblSurveyParticipantAnswer
SurveyParticipantAnswerID
SurveyQuestionID
ParticipantID
SurveyParticipantAnswer

To see the questionnaires associated with each participant .......
1 Create a main form named FrmParticipant, based on TblParticipant
2 Create a query named QrySurveyParticipantAnswer that includes
TblSurveyParticipantAnswer, TblSurvey, TblSurveyQuestion. Sort on SurveyDate
then SurveyQuestionNumber
3 Create a form named SFrmSurveyParticipantAnswer based on
QrySurveyParticipantAnswer
4 Make SFrmSurveyParticipantAnswer a subform on FrmParticipant. Be sure
the LinkMaster and LinkChild properties of the subform control are set to
ParticipantID

You will now be able to go to a participant in the main form and see the
participant's answers to each questionaire where the questionaires are
presented in order of date and the answers are by question number in
numerical order.

Steve

 

[Metalmaniac]

Hi there, thanks very much for your reply. Sorry if my explanation was a
little vague, I just wasn't too sure what would be relevant. The tables I
have are as follows:

tblPanas
Field1 = participant number (unique)PK
Field2 = Question1
Field3 = Question2, etc.......

tblPanasC
Field1 = participant number (unique)PK
Field2 = Question1
Field3 = Question2, etc.......

tblRcads
Field1 = participant number (unique)PK
Field2 = Question1
Field3 = Question2, etc.......

tblRcadsP
Field1 = participant number (unique)PK
Field2 = Question1
Field3 = Question2, etc.......

tblParticipantNumber
Field1 = participant number PK
Field2 = date of survey entry

I want to set up the relationships such that I can make a form that has a
participant number control. I would like to set it up such that when the
participant number is entered then the data from the 4 surveys can be entered
without re-entering the participant number(because I want to ensure that the
same participant number is used for each survey)

I then want to design queries that will display all the answers from one
survey for all participants.

If you require more information please let me know and thanks again for your
time.

 

[Jeff Boyce]

If you are working with surveys/questionnaires, you might want to take a
look at the database/application created by Duane H.:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[tina]

okay, as i suspected, your tables/relationships are not normalized. i'll
second MVP Jeff Boyce's recommendation elsewhere in this thread (a tip:
advice from an Access MVP is gold, treasure it - and follow it!). i'll also
suggest that you read up on relational design principles, so you have a
better understanding of how and why Duane's design is better. for more
information, see http://home.att.net/~california.db/tips.html#aTip1

hth

 

[Metalmaniac]

WOW, thanks so much for the tips guys. I have a lot to work with here now.

 

[Jeff Boyce]

tina

Too kind, thanks! (there's a lot of folks out there with a lot of
experience ... I keep learning something new every day!)

Jeff

 

[tina]

oh, well, Jeff, i never said casting nuggets precluded gathering some for
yourself! i know i've got a nice little hoard, and i add to it whenever i
can. i even scatter a grain myself from time to time - though i tend to
scrape them off somebody else's nuggets...what can i say, i'm on a budget!