BOM (Bill of Materials) Construction

[Guest]

Hello. I have been reviewing some of the threads contained here and have
found that a decent way to construct a BOM is to use some ideas used "Joe
Celko's SQL for Smarties". While this is a book I have skimmed through, I
need a little more detial in one area:

The BOM's I need will consist of around 900 parts, some components and some
finished goods. Naturally, given this size, I do not want to chart out the
tree traversal for each part unless I must. Is there a simple way to
calculate this information, so that I may use the ideas of Celko? Or, even a
complicated way that does not require drawing it out? Or, perhaps, another
way to construct a multi-level BOM in Access?

Any assistance is VERY much appreciated. Let me know if you have any
questions. Thanks!

 

[Allen Browne]

Shane, how deep is your BOM nested?

Whenever I have had to do one of these, I have cheated and defined a fixed
maximum depth. Then I loop through the structure using VBA code, and
resolving it into a non-normalized temp table. IME, this is the best way to
avoid the infinite recursion problem where are part is incorrectly entered
as its own ancestor. If the code can't resolve it in the depth required, it
quits showing a report of the problem items, and the user can't complete
that task without first fixing the problem(s). If it does resolve, then you
have a temp table that you can use as the tree structure. (It contains just
the primary key values to the fixed depth of course.)

 

[Guest]

Overall, the maxmimum depth is 4 levels.

And, just to be clear are you talking about another method of constructing
the BOM, instead of a technique such as tree traversal? If you could give me
an example of some of this code/more explanations. Thanks.

 

[Allen Browne]

The database contains and relies on a Bill Of Materials structure, where
Part 9 contains 4 of Part 8
Part 9 contains 2 of Part 6
Part 6 contains 3 of Part 7
Part 6 contains 1 of Part 2
and so on, where the part it contains may itself consist of other parts.

I create a table in the front end and populate it with code so that:
Part 9 contains 4 of Part 8
Part 9 contians 6 of Part 7 (through Part 6)
Part 9 contains 2 of Part 2 (through Part 6)
In this table, each part on the right is elemental (no further breakdown),
so based on this you can determine:
a) what stock is needed, and
b) what stock exists.

In the attached example, a "course" is a product that consists of other
products. (I've broken the rules and used an attachment due to the very long
lines of SQL statements.)

HTH