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

    relationships 3 tables (2003)

    Hi, I have 3 tables: students, terms, and rotations......which I believe should relate to each other in a many-to-many fashion. I don't know if need two link tables or just one (with 3 primary keys)? For each student i'd like to be able to select the term (1 - 4) and for each term, select a rotation. Thanks, Van

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

    Re: relationships 3 tables (2003)

    You need a table StudentTerms that links to the Students table on the Student ID field and to the Terms table on the Terms ID field. Each record in this table represents a student attending a term.
    And you need a table StudentTermRotations that links to the StudentTerms table on both the Student ID and Term ID fields and to the Rotations table on the Rotation ID field. Each record in this in this table represents a rotation for a specific term and student.

  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 3 tables (2003)

    so the Student form is the main form, with the subform being the StudentTerms (with StudentTermRotations as a subform in the subform).....is that correct?

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

    Re: relationships 3 tables (2003)

    You can view the data in different ways, for example a main form based on the Students table and a subform based on the StudentTerms table, if you want to display the terms for each student. Or a main form based on the Terms table and a subform based on the StudentTerms table if you want to display the students for each term.
    You could use a sub-subform for the StudentTermRotations table, or a second subform on the main form linked to the first subform.

  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 3 tables (2003)

    Thanks Hans. I'm having trouble creating the relationship links between the two junction tables (StudentTerms and StudentTermRotations). Access doesn't seem to allow....keeps creating a new table entitled StudentTerms_1. ??? Each junction table should have 2 and 3 primary keys respectively right? Van

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

    Re: relationships 3 tables (2003)

    When you drag a line in the Relationships window from Student ID in StudentTerms to Student ID in StudentTermRoations, Access pops up a window. Add Term ID on both sides in this window. When you click OK, there will be two lines joining the two tables, indicating that they are joined on 2 fields simultaneously instead of separately.

    See the attached database.

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

    Re: relationships 3 tables (2003)

    thanks muchly Hans, it's easy when you know how. cheers, Van

Posting Permissions

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