Assembly/Sub-assy/Part tree

[Walt E]

I posted this question in the Reports section, but am thinking maybe it is
more of a basic design issue than report issue. I have a basic db of part
numbers and sub-assys and assys, and have a "where
used" report. It works fine, but I can't figure out how to go more than two
levels deep. Example: Assy # xxx1 is made up of Sub-Assy yyy1 and part
zzz1 in the example below - these two levels are easy. But Sub-Assy yyy1 is
made up of part zzz2 and zzz3. I would like to show the breakdown of the
Sub-Assy yyy1 in the same report - or a tree (for lack of a better term).
The breakdown for any given Assembly could go as many as 5 sub assemblies
deep. Any suggestions?

Assy xxx1
part zzz1
Sub-Assy yyy1
part zzz2
part zzz3

 

[Allen Browne]

Walt, run a search on "Bill of Materials".
Here's a starting example:
http://www.mvps.org/access/modules/mdl0027.htm

The core concept is that everything (assemblies, sub-assemblies, parts) goes
into the one Product table. You then use a related table to identify the
products that make up a product, i.e. this ProductInProduct table has
fields:
MainProductID the ProductID that contains other parts
SubProductID the ProductID of the part contained in the main
product.

So, if a main product contains 4 parts, it will have 4 records in this
table. And since the Product table contains both discrete component parts
and subassemblies, the SubProductID can be either.

That structure allows for nesting to almost an infinite level. You can add a
Validation Rule on the ProductInProduct table such that MainProductID <>
SubProductID (i.e. the product part cannot be itself.) However, that doesn't
prevent the possibility of circular entries down the nesting chain, e.g.
product A contains B, and B contains C, and then someone wrongly enters that
C contains A. This is where the reporting of this kind of structure gets
difficult: the possibility of circular, infinite, unresolvable nesting. Post
back if you want more on this.

 

[Ken Sheridan]

You'll find another example attached to an old post of mine at:

http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&webtag=ws-msdevapps&tid=23133

It’s the second post in the thread.

The Zip file includes two Access files, one which creates a BOM down to
virtually any number of levels, and one which contains a report in the sort
of format you have in mind, but limits it to 9 levels (dictated principally
by the available page width).

Ken Sheridan
Stafford, England

 

[Jamie Collins]

Walt, run a search on "Bill of Materials".
Here's a starting example:
http://www.mvps.org/access/modules/mdl0027.htm

The core concept is that everything (assemblies, sub-assemblies, parts) goes
into the one Product table. You then use a related table to identify the
products that make up a product, i.e. this ProductInProduct table has
fields:
MainProductID the ProductID that contains other parts
SubProductID the ProductID of the part contained in the main
product.

That's one approach using the adjacency list model, which has its
issues (denormalized design being one of them).

The mpvs site also advocates the nested sets approach ("up to 1000
times faster than their equivalent methods"). See:

http://www.mvps.org/access/queries/qry0023.htm

Jamie.

--

 

[Walt E]

Thank you Allen, Kamie, and Ken for your response. I've gone to the websites
you've recommended and downloaded the two BOM zip files I found. I'm sure
that I'll be able to learn a lot from this information and end up where I
want to go.

Ken - fyi, the link you provided took me to the main netscape community
forum page and try as I might, I couldn't get to the post you referenced. I
tried going into the MS Office/Access area and search for 23133, but that
didn't work either.

Thanks again for your help - you all do a great job helping those of us
trying to use Access/MS Office.