If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Assembly/Sub-assy/Part tree
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 |
#2
|
|||
|
|||
Assembly/Sub-assy/Part tree
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Walt E" wrote in message ... 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 |
#3
|
|||
|
|||
Assembly/Sub-assy/Part tree
You'll find another example attached to an old post of mine at:
http://community.netscape.com/n/pfx/...apps&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 "Walt E" wrote: 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 |
#4
|
|||
|
|||
Assembly/Sub-assy/Part tree
On Aug 11, 3:25 am, "Allen Browne" wrote:
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. -- |
#5
|
|||
|
|||
Assembly/Sub-assy/Part tree
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. "Walt E" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|