You can see in this example they have two columns called “lft” and “rgt”. This is from the A***ss sql so it will be slightly different.
SELECT P_2.MemberName, Exp(Sum(Log(P_1.Qty))) AS RequiredQty FROM P, P AS P_1, P AS P_2 WHERE (((P.MemberID)=[RootNodeID]) AND ((P_1.lft) Between [P].[lft]+1 And [P].[rgt]) AND ((P_2.lft)=[P_2].[rgt]-1 And (P_2.lft) Between [P_1].[lft] And [P_1].[rgt])) GROUP BY P_2.MemberName Here is a link to an A***ss database with celkos bom. Good to look at. http://access.mvps.org/access/queries/qry0023.htm Dan From: Brad Davidson Sent: Thursday, March 22, 2012 3:54 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Exploding BOMs, Revisited I’ll pipe in on this, am very interested in this discussion and had the same questions as Karen. We’re an FAA repair station and BOM coding has been a constant evolution with us over the years: an end item component can have several assemblies, and each assembly can have several sub assemblies, with each hierarchal level having their own piece parts, and potential usage of all across several disparate end item units within the same OEM family, and in some cases, across OEM’s! I’ve had to code for set (finite) level of hierarchy, but, it’s possible additional level(s) will be introduced in future manufacture designs. Thanks to all for sharing in this topic. Brad Davidson Aero-Craft Hydraulics, Inc. From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Thursday, March 22, 2012 2:10 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Exploding BOMs, Revisited Didn't even wait for a bathroom break to read this! My comments: I don't understand what his Q1-Qn column are for in his table. I'm hoping he doesn't mean that you would need to create a finite amount of columns for levels.... I'm interested in your quote: "and self-joining allows any part to be tracked to n depth." Is that just a thought of yours, or did Codd have an example of how a self-join would achieve that? Karen In a message dated 3/22/2012 1:23:41 PM Central Daylight Time, [email protected] writes: All: Bathroom reading, perhaps. Last week's thread dealt with the question of how one might process a BOM of any depth; but the introduction of 'nested sets' and Karen's use yesterday of a recursive self-join ("SQL Help") tripped a memory. Dr. Codd dealt with the BOM question very early on in his development and exposition of the relational model, and gave the issue a meaningful review (Ch.28) in his book: 'The Relational Model for Database Management Version 2' (Codd, E.F. Addison-Wesley 1990). In response to critics who held forth that the relational model could not handle BOM-type hierarchies, Dr. Codd wrote (p453): "A hierarchy may be an adequate representation in a few manufacturing environments, but in many - probably most - it is not adequate. In these latter environments, a particular type of part may be an immediate component of several types of parts, not just one. A second, all-too-rapid conclusion is that a DBMS is needed that exposes network-structured data to users. "In fact, in 1970 I presented [Codd 1970] an extremely simple representation of product structure in the relational model by means of the COMPONENT relation: COMPONENT (SUB_P# SUP_P# Q1 Q3 ...Qn) "where SUB_P# denotes subordinate part number, SUP_P# denotes superior part number, and Q1, Q2, ..., Qn denote immediate properties of each particular subordination. "Incidentally, if (p1,p2,q1,q2,...,qn) is a row of the COMPONENT relation, then part p1 is an IMMEDIATE component of part p2." [And here's the rub:] "The fact that part p is a non-immediate component of a part p7 (say) is not directly represented in the COMPONENT relation. A fact of this type can be easily derived by the RECURSIVE JOIN operator ... "In a computer-oriented sense, this kind of representation in a relation is adequate for all kinds of networks, whether they happen to be pure hierarchies, acyclic nets, or nets in which cycles may recur..." "...the relational representation is probably not the best for use by human beings, for whom graphs drawn as pictures appear to be more comprehensible and suitable. However, that subject can be discussed separately, and handled by separate code, when presenting data to people in a form more consumable by people (e.g., the formatting of reports)-it has very little relevance to mechanizing the management of data." **************** What Dr. Codd appears to say is that ANY LEVEL of BOM subordination can be achieved with the simple "COMPONENT" structure described above. No extension (add'l columns) is required to recognize n LEVELS of subordination; and self-joining allows any part to be tracked to n depth. And he addresses the applicability of 'nested sets' to this application. It didn't take long last week to figure out that a) the nested set approach is the 'visual', 'human-suitable' approach which he considers representational, b) that the right/left adjacency proposed within the nested set model wraps a layer of 'metadata' around base data values, and c) where a given part appears in two or more nesting paths, the model chokes, at best requiring that an additional layer of 'metadata' be constructed around data values. Interestingly, the nested set approach does seem to have value in situations where the child-parent relationships are eternally fixed, for example, in a condominium complex where the number of buildings and the number of units within each building will never (barring catastrophe) change; or where PHYSICAL adjacency needs to be described, for instance, in a mapping application. Something to play with. Thanks to Karen, Ben, A.G. and others who sent me down this path. Yours in learning Bruce Chitiea SafeSectors, Inc.
