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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|