Creating Tables

[Fry1330]

Hiya iam new one here, and fairly new with access. I got my self into a mess to what is proberly very simple to solve. I have 5 different products, which have 18 components for each product, each component has a part number and a price. Iam finding it difficult to arrange this data into tables in 3rd normal form. Do I have a table for each product, then a table for components, then a table for part number and price, but how would I create relationships for these?? Please help its driving me mad, any help would be greatly appricated.

 

[Bob Miller]

Hiya iam new one here, and fairly new with access. I got my self into a mess to what is proberly very simple to solve. I have 5 different products, which have 18 components for each product, each component has a part number and a price. Iam finding it difficult to arrange this data into tables in 3rd normal form. Do I have a table for each product, then a table for components, then a table for part number and price, but how would I create relationships for these?? Please help its driving me mad, any help would be greatly appricated.

If no product has components used in another product, I would have two tables. First would be the tblProducts with ProductID as the primary key and other information as needed. The second table would be tblComponents with ComponentID as the primary key and ProductID as the foreign key. If components can be shared by more than one product I would add an additional table with ProductID and ComponentID to tie components with the product and not have the ProductID in the tblComponent.
In the first case, you would enter your data in a main (Single) form with tblProducts as the recordsource. You would have a (Continuous) subform on this main with tblComponents as the recordsource and linked to the main by ProductID (parent and child.)
When you enter the product in the main form all components that are added in teh subform will be tied to the product.
If your scenario is the second, reply back and I'll try to explain how to do that.
Hope this helps, Bob

 

[Fry1330]

Thanks Bob,
I kinda understand, but not very well. Some products do share the same part number for the component. A handle for a mower has part number H10O and price of £4.50, for a strimmer it is part number H200 and price £4.50, for a hedger it is part number H100(SAME AS MOWER) and has price £4.50. The same applies but varies for another 18 components, some components such as electric motor are not required by the mower. Hope this helps, thanks alot, james

 

[Fry1330]

I think ive got it, would this work?? Thanks alot for any help


tblproduct
----------------
ProdID ->pk
Product (MOWER, STRIMMER, ROTOV'R,HEDGER,SCARIFIER)

tblcomponents
------------------
CompID ->pk
Component(Handle,cable,petrol tank.....etc)
PartNo
Price

tblProductComponents
------------------------------
ProdCompID ->pk
ProdID->fk
CompID->fk