On Mon, 15 Feb 2010 10:25:01 -0800, mglg01
wrote:

I need help designing a database for parts and products. I'll give you a
brief description of the scenario:

There are thousands of parts, and hundreds of products. I want a database
that has the following:
-a list of the parts - with description, product number, color, length,
etc.
-a list of the products with the associated parts included in the product
-If I change a description of a part, I want it to change in the
products and all related aspects

I know I need a part table, but I don't know how to incorporate the products
- because the parts can be used for more than one product.

Any help, comments is greatly appreciated!

Thank you

There are several possible scenarios here. A typical scenario is that each
Product consists of many Parts, and each Part can appear in many Products;
this needs three tables -

Parts
PartNo primary key
Description
Color
other part attributes

Products
ProductID primary key
ProductName
other attributes of the product as a whole

ProductParts
ProductID link to Products
PartNo link to Parts
any info about this part as it pertains to this product, e.g. number
needed

A Form to enter, display and edit this could consist of a mainform based on
Products with a subform based on ProductParts, with a combo box to select the
part; the combo could contain multiple fields for the different part
attributes.

If a Part can itself consist of other Parts, or if one Product can be a Part
of another Product, you'll need Fred's BOM solution.

--

John W. Vinson [MVP]