Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    relationships (2003)

    Hi all, I am having some trouble with table relationships. I want Subjects and Studies to relate many-to-many. This I can do with an junction table (tblJT)....(although, i'm not sure how this table gets populated with data...does it need to be???). Anyway, the next problem is that each subject can complete up to 8 different questionnaires (tbl1, tbl2,.....tbl8).....for a particular study them may be enrolled in. (note: a subject can be enrolled in more that one study, which is why the many-to-many). Am I correct in having one-to-many relationships betw subjects and the questionnairs? Any help would be much appreciated. Regards, Van

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    The usual way to enter data into the 'junction table' of a many-to-many relationship is through a subform. For example, you could have a main form based on the Subjects table and a subform based on the junction table. The subject ID field would be filled in automatically on the subform by Access (through the linkage of the main form and subform), and the user would select a study from a combo box that has the Studies table as row source.

    Do you really need 8 tables for the questionnaires? It depends on the structure of the questionnaires, but I'd try to use a single table like this:

    <table border=1><td>SubjectID</td><td>StudyID</td><td>QuestionnaireID</td><td>QuestionID</td><td>Response</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td align=right>3</td><td align=right>1</td><td align=right>1</td><td align=right>1</td><td align=right>2</td><td align=right>5</td><td align=right>...</td><td align=right>...</td><td align=right>...</td><td align=right>...</td><td align=right>...</td></table>
    As you see, this is in fact another junction table.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2003)

    Hi Hans, I've got the subject table and study table setup ok using the junction table as you've advised. The next big problem is the questionnaires....I don't know where to go in regards to setting up the relationships, primary keys, etc. Yes, I think I do need 8 questionnaires. Each is quite complicated, distinct, and some have upwards of 70 fields. I have them into 8 separated tables and 8 separate forms. Each subject can complete the same questionnaire more than once....if, for example, they are in more than one study. Do i need more junction tables? one for each questionnaire? What should the PK (s) be for the questionnaire tables? Or, do these tables need to be brought together in one massive query? Many thanks for your help. Regards, Van

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    Once again, it depends on the structure of the questionnaires. It *is* possible to use a separate field for each question, but I'd try to avoid that. But I'd need to have detailed information for a detailed answer.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2003)

    Hi Hans, I have attached a sample of the database. There are 3 tables: 2 questionnaires and 1 subject info table. I'm unsure where to go from here in regards to relationships, PK, etc between these tables. Thanks again, Van
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    I don't understand the tables. For example, why is there a field 'gender' in both tblAthens and tblBPI. And what is the meaning of all those fields in tblSubjectInfo?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    Another point: I see that you have lots of text and memo fields. In my opinion, the use of text and memo fields in a questionnaire should be kept to an absolute minimum. It's very difficult to analyze the replies in such fields, because you have no control over what the user enters - the same reply could be phrased and spelled in different ways. It's much better to let the user choose from a limited number of possibilities.

  8. #8
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2003)

    Hi Hans, I agree with your points regarding the memo/text fields, however I am not the author of these validated questionnaires. I've got 'gender' in and 'age' in all tables b/c the user wants to be able to export a particular table (Athens for example) to a spreadsheet for statistical analysis. I see your point though...you are saying that 'gender' and 'age' are better being entered only once...in the subject table right? All the fields in the the tblSubjectInfo are individual questions regarding a variety of domains: ie. sleeping habits, medical status, drug habits, etc. Van

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    The subject info table should contain all "fixed" personal information about the subject, such as gender (unless you're studying gender change, of course), date of birth (you can calculate the subject's age from this and from the test date for a questionnaire), etc. Whether sleeping habits, medical status etc. are to be considered as "fixed" depends on the purpose of the study - if you want to analyze their development over time, they should be in the questionnaire tables instead of in the subject info table.

    The SubjectID field is the primary key of the subject info table, and this field should be used to link the subject info table to each of the questionnaire tables.

    If you want to include personal information when exporting a questionnaire, create a query based on the subject info table and the questionnaire table, joined on the SubjectID field, and export the query.

    As for the primary key of the questionnaire tables, this should be a composite key consisting of two (or perhaps more) fields. One of these fields must obviously be the SubjectID field. If a subject can fill in a questionnaire only once for a specific study, the StudyID field should be the other field in the primary key. If a subject can fill in the same questionnaire more than once for the same study, you need a third field in the primary key: a sequence number, or perhaps the test date.

  10. #10
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2003)

    Thanks Hans, that is exactly the help I'm after. One last question: On the questionnaire form, when I select the subjectID, how do I filter the studyID (likely a combo box) so that it only shows the studies that the chosen subject is enrolled in? Do I need another junction table? Regards, Van

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    Yes, if you want to register the studies a subject participates in separately, you'd need a junction table with two fields: SubjectID (linked to the subject info table) and StudyID (linked to the studies table), together forming the primary key. This table contains a record for each study a subject participates in, i.e. if SubjectID 37 participates in 3 different studies, there'd be 3 records for this SubjectID, each with a different StudyID.
    The row source of the StudyID combo box would be a query based on this table that selects the StudyIDs for the current SubjectID:

    SELECT StudyID FROM ... WHERE SubjectID = Forms!frmSomething!SubjectID

  12. #12
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2003)

    what would the primary keys be in the studies and subject tables........studyID and subjectID respectively? would the studyID be no duplicates and the subjectID be duplicates allowed? Also, in the questionnaire form, how do I get the query to refresh (update) when i move from record to record? thanks, Van

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    Primary keys by definition don't allow duplicates - they *must* be unique.
    The subjects table lists each subject only once, the SubjectID field uniquely identifies each subject.
    The studies table should list each study only once, the StudyID field uniquely identifies each study.
    In the junction table that specifies which studies a subject participates in, the primary key is the combination of SubjectID and StudyID. The same SubjectID can occur several times (with a different StudyID for each one), and the same StudyID may occur multiple times (with a different SubjectID for each), but the combinations of the two must be uniqe.
    To update the combo box, you use the On Current event of the form:
    <code>
    Private Sub Form_Current()
    Me.cboStudy.Requery
    End Sub
    </code>
    where cboStudy is the combo box listing the available studies.

  14. #14
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: relationships (2003)

    OK, here's what i've got (see attached). In the PIQ (participant info questionnaire (subject table)), i've set the subjectID as the PK (an autonumber)....that is unique. I also have a field called subjectcode (duplicates allowed), which in the PIQ form will allow for the same subject to be entered twice, but into a different study. I'm getting it, but just not getting it....??? musn't have had enough coffee this morning. Thanks, Van
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: relationships (2003)

    You shouldn't start creating tables and forms before you have fully worked out the data design.

    Is tblPIQ what you first referred to as the subject information table?
    I don't understand what SubjectCode is for - what does it mean? Since SubjectID is the PK in tblPIQ, there can be only one SubjectCode for each SubjectID.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •