Bill of Materials

[Guest]

I would like to create a database for parts and assemblies of parts (Bill of
Materials). Assemlbies would consist of parts and other assemblies which
would also contain parts. The database should also allow for multiple levels
of assemblies. For example:

Assembly-A
Assembly-B
Part-A
Part-B
Assembly-C
Part-A
Part-D
Assembly-D
Assembly-A
Part-B

What type of table and relationship structure could I use to accomplish
this? How can I create a report to show the hierarchial structure?

 

[Guest]

This is kind of a tall order but I will try to get you in the right direction.

You will need your table to keep the Bill Header (parent) information.
tblBillHeader should contain the bill ID number as it's key field. This
table will have fields like the bill ID and description. The next table
would be the bill (or assembly) Detail. This table will have the bill ID
(from the header table) and a line, sequence or item number. The two fields
together will represent the key fields. This table would have the assembly
items, quantities per parent, costs, etc.
If there are further assembly tiers, you would need to follow the same
pattern where the table would have the Bill ID, the sequence from the Detail
table and a sequence/line for the 2nd tier assembly line. Then the three
fields could be marked as key.

This is just the basics for your table structure, you will need to get that
set up correctly before tackling the forms/queries/reports.

Hope this helps.
Jackie

 

[Arvin Meyer [MVP]]

Have a look at:

http://www.mvps.org/access/modules/mdl0027.htm