Part and Product Database Help

[mglg01]

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

 

[KARL DEWEY]

Products consist of multiple parts, so your products are one-to-many parts.
Use this structure ---
tblProduct --
ProdID - autonumber - primary key
ProdNumber - text
ProdName - text
etc -

tblParts ---
PartID - autonumber - primary key
PartNumber - text
PartName - text
Description - text
Color -
Length -
etc -

tblProdParts --
ProdPartsID - autonumber - primary key
ProdID - number - long integer - foreign key
PartID - number - long integer - foreign key
QTY - number - long integer

 

[Fred]

That's more of a long term project than a single post, but here's a structure
idea at the core of it:

An "Item" table with all of your part numbers (individual components,
assemblies etc.) PK = PartNumber. Fields for all of the information /
attributes which are "one-to-one" wiht that part.


A "BOMItems" (Bill of Material) table with a record of each instance of use
of (any quantity) of a part. It would have at least these fields:

AssemblyNumber (Linked to PartNumber in previous table).
Quantity
ItemNumber (Linked to PartNumber in previous table)

 

[Fred]

I hadn't seen Karl's response when I wrote mine.

His is more simpler and more straightforward (= better) if you have two
clear tiers (products and parts) which are distinct from each other.

Mine is messier but deals with multi-level Bills of Material, and products
and where each "product" is also a part. That's the case at our company.

Fred

 

[mglg01]

Thank you all for your help

 

[John W. Vinson]

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.

 

[KARL DEWEY]

I did not think of Product as part when I posted my response. I just posted
self-join for someone and think it might apply here.

A Parts list to have a Foreign Key field for Product. Products could
themselves be parts and be in a one-to-many relationship with parts.
In the Relationship window add the table twice (Access adds a sufix of '_1'
to the table name of the second object.).
Click on the Primay Key field of first table and drag to the Foreign Key
field of the second table. Select Referential Integerity and Cascade Updates.

 

[mglg01]

Sorry but I'm kind of new with Access, so bear with me please. In your reply
you said :

"ProductParts

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

What do you mean by <link to Products>? How do I link this? Also, I just
want to make sure I am doing this right. Since each product can have many
parts, that means I am going to have records in the ProductParts Table that
goes something like this, correct?:

Product ID PartNo
A41 013
A41 014
A41 017

Am I understanding this right? Sorry that this is completely basic stuff,
but like I said I am just starting with Access and still learning

Thank you for your help!!

 

[mglg01]

and what would the Primary Key be for the ProductPartsTbl? Or do I not need
one?

 

[John W. Vinson]

Sorry but I'm kind of new with Access, so bear with me please. In your reply
you said :

"ProductParts

What do you mean by <link to Products>? How do I link this? Also, I just
want to make sure I am doing this right. Since each product can have many
parts, that means I am going to have records in the ProductParts Table that
goes something like this, correct?:

Product ID PartNo
A41 013
A41 014
A41 017

Am I understanding this right? Sorry that this is completely basic stuff,
but like I said I am just starting with Access and still learning

The Product ID (I'd name it ProductID, blanks in fieldnames can cause annoying
hassles) is the "link to products", also known as the "foreign key field".

The ProductParts table should (I'd say must) have a Primary Key, but if there
are no relations from it going on to yet additional tables, that Primary Key
can consist of the two fields ProductID and PartNo. In table design view,
ctrl-click both fields so they are both highlighted and click the Key icon;
this will allow multiple parts for each product, and multiple products for
each part, but will prevent you from entering the same product-part
combination twice. If an Doohicky product requires six Gizmo parts, you may
want to include a Quantity field; if, on the other hand, you want to uniquely
keep track of each individual part, even if there are multiple instances of a
PartNo, then you will need an additional field (an Autonumber primary key
might be simplest).

 

[Steve]

If you need your database up and running quickly, I would like to offer to
create your database for you. I provide help with Access, Excel and Word
applications for a small fee. If you want to work together on this, contact
me.

Steve
(e-mail address removed)

 

[John... Visio MVP]

If you need your database up and running quickly, I would like to offer to
create your database for you. I provide help with Access, Excel and Word
applications for a small fee. If you want to work together on this,
contact me.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP