setting up tables for assemblies

[Guest]

i'm making a new db containing varous parts and assemblies and subassemblies.
i'm not sure how to set it up. i tried to put all parts in a table and had
numerous fields for possibe components/subassemblies. i thought that i could
self join the table but have realized that i would have more queries or
fields in a query (thousands) than possible. i looked at some sample db's
(bill of materials) on mvp.org but couldn't make heads or tails of it. i
will want to print a report that lists the finished part and all the
subassemblies and their parts that will make it up. i am soooo lost on this.
your help is greatly appreciated

tera
--

 

[Nick Coe \(UK\)]

This sort of thing starts out seeming relatively straight
forward and rapidly gets somewhat complicated as you have
found <g>.

Yes, one usually starts out thinking that one table of parts
with a column for the key value of its parent part (self
join) will suffice but, as you have found, since one part
may have many different parents (e.g. a fuse in a number of
different power supply modules) and a parent will have many
component parts and sub assemblies, things seem to
complicate exponentially.

The trick usually is to have a resolver table which contains
parent and child part keys and acts to resolve the many to
many relationships.

Alternatively you could use part of the LSAR model from the
old defunct MilStd 1388-2B or current UK Def Stan 0060.
Simplified - these require a parts table (called HA) and
then the assemblies and their parts could go into the XA
(End Item table) and XB (LCN table) with an FK to the HA
table for the parts data and a column for the parent
assembly.

The BOM example at mvps.org is the best way to go. As
complex and opaque as it can appear at first the old rule of
breaking something down into more easily understandable
chunks will help in figuring out how to go about it.
Sorry I can't offer anything more substantive.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp is now Free
http://www.mrcomputersltd.com/ Repairs Upgrades

In turkey typed: