A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Part and Product Database Help



 
 
Thread Tools Display Modes
  #1  
Old February 15th, 2010, 07:25 PM posted to microsoft.public.access.tablesdbdesign
mglg01
external usenet poster
 
Posts: 4
Default Part and Product Database Help

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
  #2  
Old February 15th, 2010, 07:53 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Part and Product Database Help

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


--
Build a little, test a little.


"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

  #3  
Old February 15th, 2010, 08:14 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Part and Product Database Help

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)




  #4  
Old February 15th, 2010, 09:31 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Part and Product Database Help

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


  #5  
Old February 15th, 2010, 10:55 PM posted to microsoft.public.access.tablesdbdesign
mglg01
external usenet poster
 
Posts: 4
Default Part and Product Database Help

Thank you all for your help

"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

  #6  
Old February 15th, 2010, 11:09 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Part and Product Database Help

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]
  #7  
Old February 16th, 2010, 12:51 AM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Part and Product Database Help

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.

--
Build a little, test a little.


"Fred" wrote:

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


  #8  
Old February 27th, 2010, 04:56 AM posted to microsoft.public.access.tablesdbdesign
mglg01
external usenet poster
 
Posts: 4
Default Part and Product Database Help

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!!
"John W. Vinson" wrote:

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]
.

  #9  
Old February 27th, 2010, 05:56 AM posted to microsoft.public.access.tablesdbdesign
mglg01
external usenet poster
 
Posts: 4
Default Part and Product Database Help

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

"John W. Vinson" wrote:

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]
.

  #10  
Old February 27th, 2010, 06:42 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Part and Product Database Help

On Fri, 26 Feb 2010 19:56:01 -0800, mglg01
wrote:

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


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).
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.