Two-way related records?

[Macgyver]

I have a table (of products) which has a number of entries, any one of which could be related to multiple other entries in the same table.

I'm wondering how (if?) I can cross-relate multiple entries to each other. Specifically, for record A, I want to display a portal showing record B (along with others). Likewise, record B would display record A (along with others) in its portal.

My real question is, can I do this via a single entry in a related table? Can I establish a two-way relationship (since A is related to B, B is therefore related to A) without creating two records in a related table (one for the A > B relationship, one for the B > A relationship)?

[T-Square]

Assuming that you have a field in the table that stores the Related Product ID, then a second Table Occurrence of the Products table (Rel_Prods), linked from ProductID to Rel_Prod_ID should allow you to add a portal on the Products layout to show Related Products. This assumes that each Product can have one related product; if you have more than one related product, you'll need a Related Products table to manage it all.

[LaRetta]

This assumes that each Product can have one related product; if you have more than one related product, you'll need a Related Products table to manage it all.

Not necessarily. If this second field is mostly for reference, you can add the additional ProductIDs in another field as a multiline (or value list checkbox even). Then this multiline will relate to the one main ProductID and they will all appear in the portal.

Much depends upon what this will be used for. It is possible that you could use another field to designate their relationship instead, ie, all products ordered from the same manufacturer; or all products that are bottles, etc. So a simple field, designating their 'type' or 'manufacturer' would work. As pointed out, you may wish to relate to another table (like a Manufacturers table) if they are all manufactured by the same company.

Can you explain HOW these products are related?

For instance, I have a field that holds 'old cross-reference IDs' and I just place them in a multiline (each ID on a new line). This allows the type of cross-relationship you seem to be describing.

Can I establish a two-way relationship (since A is related to B, B is therefore related to A) without creating two records in a related table (one for the A > B relationship, one for the B > A relationship)?

If A is related to B, B is automatically also related to A.

LaRetta

Edited March 4, 2006 by Guest