Linking Records from Same Table

[Gregg Knapp]

I have a Products table containing the usual product fields such as
a product number, description, and so on w/ an AutoNumber set
as the primary key. I'm attempting to find an efficient way to link
"related" products together for query purposes.

i.e. - If someone selects the product record for a flashlight, I could
easily identify the related records such as carrying cases and bulbs.

I thought about a separate, non-related table which would simply
have fields w/ the ProductIDs, but this seems duplicative (as I'd
be storing the Product numbers in two places) and expensive in
query terms.

Would someone out there have a more simple solution? I'm sure
this is a common function. Thanks for the assist!

Gregg

 

[Allen Browne]

A related table sounds right.

This tblAssociatedProduct table will have fields:
- ProductID = the product that has associated products.
- RelatedProductID = the product associated with it.

Both those fields are foreign keys to the Product table. To create the 2
relations, drag a 2nd copy of the Product table into the Relationships
window. Access will alias it as Product_1. You can then create one
relationship each way.

This assumes that the product associations are one-way. That is, a
flashlight has 2 records for "case" and "bulb", but "case" is does not have
"flashlight" as an associated product.

 

[Gregg Knapp]

Thanks again for the tip!
I'm tinkering around w/ this right now and will try it out.

G

A related table sounds right.

This tblAssociatedProduct table will have fields:
- ProductID = the product that has associated products.
- RelatedProductID = the product associated with it.

Both those fields are foreign keys to the Product table. To create the 2
relations, drag a 2nd copy of the Product table into the Relationships
window. Access will alias it as Product_1. You can then create one
relationship each way.

This assumes that the product associations are one-way. That is, a
flashlight has 2 records for "case" and "bulb", but "case" is does not have
"flashlight" as an associated product.