Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Many-to-many relationships (XP, 2000)

    I am having some trouble understanding how to implement many-to-many relationships. I know conceptually that this calls for an intermediary table with a one-to-many relationship with each of the other two tables. However, I can
    Attached Images Attached Images

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

    Re: Many-to-many relationships (XP, 2000)

    Trying to do this is complicated. Let's take a simpler example first: two tables Table A and Table B with a many-to-many relationship. This is implemented by creating an intermediary table Table D with a composite primary key consisting of the combination of two number (long integer) fields that are linked to the primary keys in Table A and Table B.

    Table A has a one-to-many relationship with Table D, and Table B also has a one-to-many relationship with Table D. Together, these constitute a many-to-many relationship between Table A and Table B.

    We don't create a form based on Table A with a subform based on Table B.

    Depending on the perspective from which you want to look at the data, you would create either a main form based on Table A with a subform based on Table D (or on a query that includes Table D and Table [img]/forums/images/smilies/cool.gif[/img] or a main form based on Table B with a subform based on Table D (or on a query that includes Table D and Table A.)

    An example may make things clearer:
    Table A = students, with primary key StudentID
    Table B = courses, with primary key CourseID
    Table D = participation, with a primary key consisting of the combination of StudentID and CourseID.

    Each record in Table D represents a student participating in a course.

    There can be many records with the same StudentID (but all different CourseID's) - they tell us which courses a particular student attends.

    There can be many records with the same CourseID (but all different StudentID's) - they tell us which students attend a particular course.

    You could have two forms:
    1. A form based on the students table, with a subform based on the participation table, displaying the courses attended by a student. Main form and subform are linked by StudentID.
    2. A form based on the courses table, with a subform based on the participation table, displaying the students attending a course. Main form and subform are linked by CourseID.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Many-to-many relationships (XP, 2000)

    Hans

    You suggest that the intermediate table have a composite primary key, consisiting of the two foreign keys.

    I often want to allow for the possibility of multiple entries in the intemediate table for the same pair of values in the the Main tables. For example, a patron may borrow a book more than once, or a student undertake a course many times (perhaps until they pass!). Your design does not allow this, so instead I just create another autonumber field in the intermediate table and set this as the key.

    Is there any downside to doing this?
    Regards
    John



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

    Re: Many-to-many relationships (XP, 2000)

    John,

    I would decide that on a case-to-case basis. I might create yet another table with a one-to-many relationship to the intermediary table, or I might use the keys from the main tables plus a sequential number, or an autonumber (your approach.) Sometimes a complete normalization is just too cumbersome.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Many-to-many relationships (XP, 2000)

    Okay Hans, sorry but I still don't get it. I created the DB you used as an example but can't figure out how to actually use it. I have attached it for your comment. I guess the part I am confused about is updating multiple tables at the same time. Please see attached.
    Attached Files Attached Files

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

    Re: Many-to-many relationships (XP, 2000)

    General recommendations:
    - Turn off "Track Name AutoCorrect Info" in the General tab of Tools | Options... in each database you create.
    - Set the Subdatasheet Name property of all tables to [None].
    - Avoid creating duplicate indexes; Courses, for example, had two indexes on CourseID.

    I have created the subforms for you and placed them on the main forms. They are very simple continuous subforms, with a combo box to let the user select a course or student. See attached.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I very much appreciate the sample DB you attached. I have studied it and it will launch me on a total redesign of my DB. However, as I look at the forms & subforms, I don't see where the information in the subforms come from. For example, in looking the Courses form and the subform (sbfStudents), I cannot find how the students' names appears in the subform. The subform has but one field and that is the StudentID field. I see that it uses the query (qryStudents) as the row source but how is the student name retrieved? The query has two columns but the subform field is bound to column 1. I changed it to '0' to see what effect, if any, and it had no effect.


    Thanks you.


    [quote name='HansV' post='364203' date='16-Apr-2004 12:36']General recommendations:
    - Turn off "Track Name AutoCorrect Info" in the General tab of Tools | Options... in each database you create.
    - Set the Subdatasheet Name property of all tables to [None].
    - Avoid creating duplicate indexes; Courses, for example, had two indexes on CourseID.

    I have created the subforms for you and placed them on the main forms. They are very simple continuous subforms, with a combo box to let the user select a course or student. See attached.[/quote]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The combo box StudentID has the query qryStudents as Row Source. This query has 2 fields:
    - The first field is StudentID.
    - The second field is a calculated field that concatenates the last and first names of the student, separated by a comma and a space:

    SName: [LName] & ", " & [FName]

    The combo box has Column Count = 2 (corresponding to the two fields/columns in the query) and Column Widths = 0";1".
    This means that the first column has width 0", which effectively hides it, while the second column has width 1".
    The combo box displays the first non-hidden column, i.e. the column corresponding to the student name SName.
    Since the Bound Column = 1, the value stored by the combo box is not the student name displayed in the second column, but the value of the hidden first column, which corresponds to StudentID.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts
    okay, I finally got it. Love it when the light comes on. Thank you.

Posting Permissions

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