Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Junction Table problem (ACCESS 2003)

    I have the following tables - links

    Table1
    ID1 pk

    Table2
    ID1 fk
    ID2 pk
    OldAthleteName text

    Junction Table
    ID2 fk
    AthleteID fk

    AthleteNames Table
    AthleteID pk
    AthleteName text

    Athlete names used to be in table 2, however that caused unwanted duplication as many names were common in records.So I created a junction table and a distinct table of names. However I am frozen as to how I add a new Athlete name and update the junction table as well. Just adding a name in the Athlete table gets orphaned. I also need to ensure the name being added is not already in the table but I will face that afterwards. I put a subform on Form2 based on a query of both the Junction table and AthleteNames table but that led me down the path of putting a new name only in the AthleteNames table. If someone could light the way it would be appreciated, thanks

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

    Re: Junction Table problem (ACCESS 2003)

    Shouldn't the junction table have ID1 and AthleteID as fields? I don't understand the purpose of keeping Table2 and ID2.

    You could put a unique index on the AthleteName field in the AthleteNames table, if you're sure that you'll never encounter two athletes with the same name.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Junction Table problem (ACCESS 2003)

    Thanks Hans. Sorry, I think I have misled you as Table 2 has other fields in the table, being a collection of records (Sport Events). In each event is an athlete name, sometimes several names which the user was putting in with seperating commas etc. I therefore now have a table to contain all athlete names in a junction configuration. So the links for the Junction table are from Table 2 (ID2) and The AthleteNames table. (AthleteID)

    I can put a new Athlete name in the Athlete table as it has a primary key and will willingly accept a new record, however it's getting that record added to the Junction
    table thats mystifying me. Regards

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Junction Table problem (ACCESS 2003)

    Some of what you say is a little confusing, and I'm not sure you totally understand the purpose served by the junction table.

    For example, you said "Just adding a name in the Athlete table gets orphaned". This is not an orphan record! An orphan record is a record in the "many" side of a one-to-many relationship in which there is not a matching record on the "one" side.

    Your subform for Table2 should have as its recordsource just the Junction table. The AthleteID field should be represented on the subform by a combobox, who rowsource contains all the Athletes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Junction Table problem (ACCESS 2003)

    You'll have to add records to the AthleteNames table first. These records will not be linked to anything else when you create them.
    Then use a main form based on Table2 with a subform based on the junction table to add one or more of the already existing AthleteNames records to the junction table. The ID2 value will be filled in automatically.

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

    Re: Junction Table problem (ACCESS 2003)

    Thanks Mark. I have attached a Word document as it might be a clearer way to show what I am trying to do.
    Regards
    Attached Files Attached Files

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

    Re: Junction Table problem (ACCESS 2003)

    You should either use a separate form to add new athletes, or code in the On Not In List event of the combo box on the subsubform.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Junction Table problem (ACCESS 2003)

    Many thanks. Sorry if I seem to be going round in circles but I am still struggling to get to grips with the Junction method. Should the Junction Table have a primary key of its own, that would allow a new record entry of the ID2 Value and the AthleteID Value when a new Athlete name was being added in the AthleteNaes table or am I still totally confused. It seems twice the effort to first add a new name, and then select it later to add to a TXCLIPS record. I cannot use a combo box to enter a new name as the country and sport of the athlete go in the same record, and I don't think a combo will allow column entry. Many thanks

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

    Re: Junction Table problem (ACCESS 2003)

    You can use an AutoNumber field as primary key in the junction table, or use the combination of ID2 and AthleteID as primary key. This will not solve your problem though.

    If you need to supply more than the name alone when adding a new athlete, you could still use a combo box and pop up a form. See Use NotInList Event to Add a Record to Combo Box or the thread starting at <post:=146,627>post 146,627</post:>.

  10. #10
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Junction Table problem (ACCESS 2003)

    Many thanks everyone for your help. I am nearly there, however the code below works but I get the dialogue box appearing saying the text entered is not on the box/combo. Any ideas, thanks

    Private Sub Combo158_NotInList(NewData As String, Response As Integer)
    Dim db As DAO.Database, rst As DAO.Recordset, SQL As String, TP As Long

    Response = False 'acDataErrContinue
    If MsgBox("The Athlete " & NewData & " is not in list. Add it?", vbYesNo) = vbYes Then
    Set db = CurrentDb()

    ' PUT NAME INTO ATHLETE NAMES
    SQL = "Select * From AthleteNames"
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
    rst.AddNew
    NewData = UCase(NewData)
    rst![Athlete] = NewData
    rst.Update

    ' Get AthleteID
    rst.MoveLast
    TP = rst![AthleteID]

    Set rst = Nothing
    Response = acDataErrAdded
    DoEvents

    ' NOW UPDATE THE JUNCTION TABLE
    SQL = "Select * From Junction"
    Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

    rst.AddNew
    rst![ID2] = Forms!MainForm1!Subform1.Form.ID2
    rst![AthleteID] = TP

    rst.Update
    Set rst = Nothing
    Response = acDataErrAdded
    DoEvents
    Me.Combo158 = Me.Combo158.OldValue

    Else
    Me.Combo158 = Me.Combo158.OldValue
    Response = acDataErrContinue

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

    Re: Junction Table problem (ACCESS 2003)

    The combo box is on the subform bound to the junction table, correct?
    If so, you should *not* try to add a record to the junction table. It is sufficient to add the athlete to the AthleteNames table and specify

    Response = acDataErrAdded.

    Access should take care of the rest.

  12. #12
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Junction Table problem (ACCESS 2003)

    Hi hans. I removed the subform on the TXCLIPS subform as I was getting nowhere. I have a listbox on the TXCLIPS subform which shows the Athletes related to the record. Its code is

    ' Listbox
    SELECT AthleteNames.AthleteID, AthleteNames.Athlete, AthleteNames.Country, JUNCTION.ID2
    FROM AthleteNames INNER JOIN JUNCTION ON AthleteNames.AthleteID = JUNCTION.AthleteID
    WHERE (((JUNCTION.ID2)=[forms]![Mainform1]![Subform1].[form].[ID2]))
    ORDER BY AthleteNames.Athlete;

    The combo box is also on the subform TXCLIPS. Its code is

    ' Combo Box
    SELECT AthleteNames.AthleteID, AthleteNames.Athlete, AthleteNames.Country, AthleteNames.SportorSports
    FROM AthleteNames
    WHERE (((AthleteNames.SportorSports)=[forms]![Mainform1].[Form].[SportorSports]))
    ORDER BY AthleteNames.Athlete;

    It seems to add both the name and entry in the junction table but just creates that the text entered is not in the list?

    Am I going down a slippery slope?

    Thanks

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

    Re: Junction Table problem (ACCESS 2003)

    You'll have to set the SportOrSports field as well as the Athlete field while adding a record to the AthleteNames table, since the row source of the combo box looks for athletes with a specific SportOrSports value.

  14. #14
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Junction Table problem (ACCESS 2003)

    Eureka!! Many thanks Hans. I took the fields out of the combo and it works like a dream. I will sort out tthe other fields another way. Once again thanks to you, and the others that came in. Best 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
  •