Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have done it before, but cannot remember the process I followed to do it.

    I have two related forms. The parent form table has a primary key PK1
    The subform table has PK1 as its foreign key, and PK2 as its primary key.

    I am trying to create a juntion table to avoid the duplications held in the child table.

    The child table looks like this:

    Can someone tell me the process, thanks
    Attached Images Attached Images

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you have a parent and a child table there is no need for a junction table. A junction table typically joins 2 parent tables, e.g., a book table and an author table. The junction table would have, at a minimum, the primary keys from the two main tables as foreign keys, thus forming two one-to-many relationships.

    In your case you could set up your primary table as is with PK1 as the primary key and then set up an athlete table with the athlete's names, etc. with PK2 as the primary key. The junction table would include PK1 and PK2 as foreign keys plus any other information you need. Then create a one-to-many relationship between the Primary Table and the junction table and a one-to-many relationship between the Athlete table and the junction table thus creating a many-to-many relationship. HTH.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks Bill. I seem to go down the road of thinking that if the same data is getting repeated in a table then one adopts a junction table, to avoid repitition and reduce resources. However to me it looks like I end up using more?, ading a further table. Maybe I should forget it, as other fields get duplicated ie Towns, Counties. How far do you go with it. Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you'd like more help, you'd have to tell us what the purpose of your child table is - what is the role of PKJ2 and of the athlete name?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This looks Race Entries to me, where PK1 represent Races, and PK2 are entries.

    If the ony information you have about an Athlete is a name, you could get away with the current setup, but it would be better to create an Athletes table. You then have a many-many relationship between Races and Athletes. Each race has many Athletes in it, and each Athlete enters many Races.
    The current table, entries, becomes the junction table.

    Create a query that extracts the distinct athletes from the entries table, then turn it into a make table query to make an athletes table. Add an autonumber Primary Key PK3.
    Add PK3 to the entries table to represent the Athlete.
    Create a query that joins entries to athletes on athlete name, and use that to update PK3 in entries to the PK3 of the athlete.
    Then delete the Athlete field from the entries table.
    [attachment=83677:races.gif]
    Attached Images Attached Images
    Regards
    John



  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks Hans & John.

    I will follow John's way to create a junction table. Although at present the only data in the field is the athletes name, other data could get added later, and the job would be done. Thanks again, have a good weekend. Regards

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry I'm back.

    I can understand the firts part:

    Create a query that extracts the distinct athletes from the entries table, then turn it into a make table query to make an athletes table. Add an autonumber Primary Key PK3.

    Add PK3 to the entries table to represent the Athlete.

    Its this bit I cannot work out?
    Create a query that joins entries to athletes on athlete name, and use that to update PK3 in entries to the PK3 of the athlete.

    I have attached my relationships window showing the original tables Table1 and Table2. I also show my new table Athletes, and a Junction Table. Can you show me what the query looks like, is it done in a query grid or in code. Thanks
    Attached Images Attached Images

  8. #8
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay I got there. Did it longhand, works. Thanks

    Private Sub Command166_Click()
    ' Fill junction table
    Dim db As DAO.Database
    Dim Rst1 As DAO.Recordset, Rst2 As DAO.Recordset, Rst3 As DAO.Recordset
    Dim MySql1 As String, MySql2 As String, MySql3 As String

    Set db = CurrentDb()

    MySql1 = "SELECT ATHLETES.PK3, ATHLETES.Athlete FROM ATHLETES;"
    Set Rst1 = db.OpenRecordset(MySql1, dbOpenDynaset)

    MySql2 = "SELECT Table2.PK1, Table2.PK2, Table2.Athlete, Table2.Group FROM Table2;"
    Set Rst2 = db.OpenRecordset(MySql2, dbOpenDynaset)

    MySql3 = "SELECT JunctionTable2.JunctionID, JunctionTable2.PK1, JunctionTable2.PK3 FROM JunctionTable2;"
    Set Rst3 = db.OpenRecordset(MySql3, dbOpenDynaset)

    Rst1.MoveFirst: Rst2.MoveFirst
    Do While Not Rst1.EOF ' Go through Athletes Table

    Do While Not Rst2.EOF ' Go through Table 2

    If Rst2("Athlete") = Rst1("Athlete") Then

    Rst3.AddNew
    Rst3("PK1") = Rst2("PK1")
    Rst3("PK3") = Rst1("PK3")
    Rst3.Update

    End If
    Rst2.MoveNext
    Loop

    Rst2.MoveFirst
    Rst1.MoveNext
    Loop

    Set Rst1 = Nothing
    Set Rst2 = Nothing
    Set Rst3 = Nothing
    Set db = Nothing


    End Sub

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    This is what I had in mind. My assumption was that you actually had the table already, but that you needed to include a PK3 value so you could then delete the athlete name.
    [attachment=83696:UpdatePK3.gif]
    Attached Images Attached Images
    Regards
    John



  10. #10
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks John for all your help, as I say I got there so alls well. Thanks again, regards

Posting Permissions

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