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

    Junction table problem (MSACCESS 2003)

    Unfortunately the DB is too big to attach, but I am having problems generating a Juntion table. I have attached a word document showing the process and hoping somebody can spot the problem. Many thanks
    Attached Files Attached Files

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

    Re: Junction table problem (MSACCESS 2003)

    Sorry, but I don't follow.

    A junction table creates a many-to-many relationship between two other tables. It is not clear to me from what you have posted what the two tables are that you are linking.
    One table is athletes (of which your sample holds just one record.)

    You also have AllAthletes which contains lots of repetition of the same data. What is different about the records?

    I could imagine having a Sports table, then having a junction table that linked athletes to sports.
    Regards
    John



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

    Re: Junction table problem (MSACCESS 2003)

    I don't understand either.

    Your query QUERYTOP or QRYTOP (you're not consistent) seems overly complicated if the table AthleteNames contains unique names.

    After the line

    rsQRYTOP.FindFirst ("Athlete = '" & Replace(rsAllAthletes.Fields("Athlete"), "'", "''") & "'")

    you don't check whether a record was actually found. If not, the following line will use the AthleteID field from the last successful FindFirst. Perhaps you should use this:

    rsQRYTOP.FindFirst "Athlete = " & Chr(34) & rsAllAthletes.Fields("Athlete") & Chr(34)
    If rsQRYTOP.NoMatch Then
    MsgBox "No match found for " & rsAllAthletes.Fields("Athlete")
    Else
    rsJUNCTION.Fields("AthleteID") = rsQRYTOP.Fields("AthleteID")
    End If

    But again, I don't understand what your code is trying to accomplish.

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

    Re: Junction table problem (MSACCESS 2003)

    Thanks both, sorry for the confusion. The database originally had two related tables. In the second table there was one field that was used as an athlete names field, and instead of just one name being entered, the users entered several names seperated by commas etc, in fact a whole maze of seperators. I wrote some code that extracted all the names and the ID of the record (ID2) that existed, and put this data into a table named AllAthletes.

    I then wrote a query to get the distinct athlete names from the table, giving the athlete an ID number (AthleteID), which put this data into another table called Athletenames.

    The final code was to make a junction table called JUNCTION by taking the AthleteID, and ID2 value via a query.

    So my new junction table would contain AthleteID, ID2.

    The new junction table joins to the original 2nd existing table via ID2 and to the new Athletenames table via AthleteID

    Does that make it clearer, thanks

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

    Re: Junction table problem (MSACCESS 2003)

    I have just done a flowchart of what I am trying to do, and will redo the method I have.
    Loop Athletenames
    Loop AllAthletenames
    When both matched then add into Junction table
    Put in the AthleteID from AthleteNames table into Junction table
    Put in the ID2 value from AllAthleteNames
    etc, etc.

    I think it's an easier method to build my new Junction table?
    It's only a one time operation to get a Junction table filled, this time correctly I hope

    If I get stuck I will get back. Thanks again

  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 (MSACCESS 2003)

    I have now found what I wanted, however it's very slow as it loops a complete recordset each time to find names.

    Do While Not rsAthleteNames.EOF

    rsAllAthletes.MoveFirst

    Do While Not rsAllAthletes.EOF

    If rsAllAthletes.Fields("Athlete") = rsAthleteNames.Fields("Athlete") Then

    rsCHECK.AddNew
    rsCHECK.Fields("AthleteID") = rsAthleteNames.Fields("AthleteID")
    rsCHECK.Fields("Athlete") = rsAthleteNames.Fields("Athlete")
    rsCHECK.Fields("ID2") = rsAllAthletes.Fields("ID2")
    rsCHECK.Update

    End If

    rsAllAthletes.MoveNext
    Loop ' AllAthletes

    rsAthleteNames.MoveNext
    Loop ' Athletenames


    There are multiple matches in the AllAthletes table, but even as they are in ascending order I cannot see how to exit the do at the right time. Any ideas, thanks

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Junction table problem (MSACCESS 2003)

    Is the "right" time when you find a match?

  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 (MSACCESS 2003)

    Hi Patrick, thanks. Yes when its a match the record gets put in the table. I have now changed the table name to junction, so it makes more sense what it is doing.

    Do While Not rsAthleteNames.EOF

    rsAllAthletes.MoveFirst

    Do While Not rsAllAthletes.EOF

    If rsAllAthletes.Fields("Athlete") = rsAthleteNames.Fields("Athlete") Then

    rsJUNCTION.AddNew
    rsJUNCTION.Fields("AthleteID") = rsAthleteNames.Fields("AthleteID")
    rsJUNCTION.Fields("ID2") = rsAllAthletes.Fields("ID2")
    rsJUNCTION.Update

    End If

    rsAllAthletes.MoveNext
    Loop ' AllAthletes

    rsAthleteNames.MoveNext
    Loop ' Athletenames

    Basically its first looping a table that contains distinct athlete names, and an athleteID number.
    It then cylcles through another table where records exist containing all athlete records. When it finds a record with the athlete name in it puts the data into the junction table, the AthleteID and ID2.

    As it is at the moment, the second cycle starts from beginning to end each time, where there are 18,000 records plus, you can imagine it's taking some time each time it loops. Best regards

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Junction table problem (MSACCESS 2003)

    Why not join the tables in an INSERT query?

  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 (MSACCESS 2003)

    Many thanks Patrick, I had not considered that. I did a join between the two tables on the Athlete field and inserted data into Junction, hey presto done. Many thanks again, have a good weekend

Posting Permissions

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