Table linking

[ZOR]

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.

[ZOR]

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

[WendellB]

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.

[ZOR]

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

[WendellB]

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.

[ZOR]

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.

[ZOR]

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