Results 1 to 7 of 7

Thread: Table linking

  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a main table that contains barcode numbers. I then have a related table containing the information related to the barcode record. However one of the fields in the second table has a field which says this record is related to two/three other barcodes. It does not state the Barcodes its related to, so I am trying to do a manual link of barcode numbers this record is related to. Unfortunately the database already contains data, so if a junction table is to be used then I'm not sure how I proceed. I am trying to end up with the user clicking on any barcode and it also showing others that might be related. Hope I have explained it clearly, thanks.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Am I going in the wrong direction with this arrangement? I am hoping I can put related Barcode numbers in the Related Table. Does it look as though it would work, or will it stop me generating further records in the MainData table. Would like to see the wall comming before I hit it. Thanks
    Attached Images Attached Images

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I don't see the reason for have "Barcode" in the RelatedTable - it seems to me you want just two fields in the table so that each given barcode can be related to any other barcode - the only other kind of field might be some sort of indications as to what the relationship between the two is. And the data in your MainTable is clearly not relational and should be replaced with records in the junction table.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your reply. Originally the Barcodes table was linked to the maindata table on a one to many. The related barcodes table is for entering other barcode numbers in while the maindata form is open. I dont seem to have lost my relational link between the Barcode Table (& form) and the Maindata table (&form). However I have not tried putting any data in the related barcodes table yet so that might throw a spanner in the works. Thats why I was checking up front.

    What I have is:

    Table 1
    Primary Key
    Barcode number

    Table 2
    Foreign Key
    Primary Key
    Data




    In table 2, I need to fill another table with barcode numbers that are related to the child record, that can be seen when selecting another Table 1 record if in another child record it has been selected as being related.

    So in the first parent record, eg Barcode 17, in the child record I have said Barcode 30 and Barcode 40 are related. If I was to select Barcode 30 in the parent table, it would then tell me Barcode 17 and 40 were related (shown in a list from the related barcodes table)

    How do I insert a table between the Parent and Child to do it? Thanks

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    OK - I think I get the idea now - you really only need two tables it seems to me. Your Linking table would have a Barcode1 field and a Barcode2 field. On the form, your Barcode table would display info about what that barcode represents. In addition you would have either a subform or a list box showing the relationships from the Linking table - probably you would want a bit of description of the related barcode as well, so a subform might be the best choice. Thus the subform would have as its datasource the Linking table joined to the Barcode table. We use this sort of structure in an application where some genealogical information is tracked. It gets a bit tricky if the relationship can work both ways however. In that case you might need a union query, which makes the data source not updateable, so you would have to resort to VBA to add Linking records. Hope this helps clarify things a bit. If not post back.
    Wendell

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks. I have progressed with the table relationsips/structure as shown earlier. A barcode is selected in the mainform which produces the relevant child form/table. The user can enter barcode numbers that have a commanality. That then fills a list on the child form so the user knows what numbers he has selected. However you have prompted me into a good idea, at the same time it will carry the update through the chain so that I have bidirectional references. Many thanks, have a good weekend.It seems very quiet out there.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Wendell, I reverted to using your suggestion, it's now a related table to the barcodes table and logs Barcode1 and Barcode2 with info. Should have followed you in the beginning as it went wrong at the end. Thanks

Posting Permissions

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