How best to create products with 1 or many subcomponents

[KerryL]

I'm not sure of the best way to create a product table (or linked tables) for products that consist of differing numbers of components. For example, consider the following two products:

PartNo Size Length Material Qty Ends Misc
==============================================
FC-001 2" 144" Galv-40 1 Grv Frm NPT

FC-002 2" 120" Galv-40 1 Grv Frm MPT
2" 24" Galv-40 2 Thrd N/A
2" 12" Galv-40 4 Grv N/A



What is the best way to create a product table that recognizes that some part numbers consist of a single component while others consist of multiple components?

Should I create a primary product table with just ProdID and PartNo fields, then link the ProdID to a second table that lists the product details for each part number?

tblProduct contains fields for ProdID & PartNo

tblProdDet contains fields for ProdID (linked to tblProduct) and Size, Length, Material, Qty, Ends & Misc


Secondly, when the frmOrder is filled out by selecting part numbers, can I fill the detail fields in with the subcomponent info without the part number being shown on each subcomponent line? IOW, make it look like my example above, if that makes sense.

TIA for any advice you can provide,
KerryL

 

[MichaelintheUK]

Hello KerryL

The packs,components problem requires an intersection table containing a minimum of 2 fields (the pack number and the component number). The key field is based on a muliple key (pack number,component number).

E.g.

tblPack(Packnumber,Description etc..)
tblComponent(ComponentNumber,Descrption,Quantity etc...)
tblComponentInPack(Packnumber,ComponentNumber)

The links are tblPack(Packnumber) to tblComponentinPack(Packnumber) (one to many)
tblComponent(ComponentNumber) to tblComponentInPack(ComponentNumber) (one to many)

It may be worth experimenting along these lines.

Please contact me if you require further details.

Thanks

Michael

 

[KerryL]

Thanks Michael, but I'm not sure I follow. I'm confused on the intersection table description.

In your example, does the tblComponentInPack sort of act as the go-between between tblPack and tblComponent?

And if I want to reference a part in a form or on a report, which of the tables do I use?

I've never done something like that so I'm a bit unclear, to say the least.

Thanks for your help,
KerryL

 

[JeremyNYC]

Yes, that's just what he's saying. Check out the Fundamentals of Relational Database Design article that I've put on my website. It's written by Paul Litwin, Access Guru, and it should be quite helpful to you.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.