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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking Many Surveys to one participant number
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. |
#2
|
|||
|
|||
Linking Many Surveys to one participant number
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 "Metalmaniac" wrote in message ... 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. |
#3
|
|||
|
|||
Linking Many Surveys to one participant number
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" wrote in message ... 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. |
#4
|
|||
|
|||
Linking Many Surveys to one participant number
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. "tina" wrote: 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 "Metalmaniac" wrote in message ... 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. |
#5
|
|||
|
|||
Linking Many Surveys to one participant number
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/O...p#Hookom,Duane Regards Jeff Boyce Microsoft Office/Access MVP "Metalmaniac" wrote in message ... 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. |
#6
|
|||
|
|||
Linking Many Surveys to one participant number
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" wrote in message ... 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. "tina" wrote: 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 "Metalmaniac" wrote in message ... 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. |
#7
|
|||
|
|||
Linking Many Surveys to one participant number
WOW, thanks so much for the tips guys. I have a lot to work with here now.
"Jeff Boyce" wrote: 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/O...p#Hookom,Duane Regards Jeff Boyce Microsoft Office/Access MVP "Metalmaniac" wrote in message ... 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. |
#8
|
|||
|
|||
Linking Many Surveys to one participant number
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" wrote in message ... 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" wrote in message ... 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. "tina" wrote: 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 "Metalmaniac" wrote in message ... 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. |
#9
|
|||
|
|||
Linking Many Surveys to one participant number
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! "Jeff Boyce" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. "tina" wrote: 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 "Metalmaniac" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|