exploded/indented bill of material (Access 97)

Topic

New Reply

In Paradox, many years ago, I created and worked with component lists used to create Bills of Material (BOM). We used a third-party program to print the actual BOM–it was so long ago and there was a different mainframe program we used after that, that I can’t even remember if we were finally able to print a multi-level BOM starting from Paradox. But that was Paradox and a mainframe program AND many years ago.

Anyway, my husband is in need of exploded (or intended) BOMs created from a base component parts list. I can visualize the single level BOM, but can Access (we have 97 at home, he has 2000 at work) report an exploded/indented BOM? Like below:

Part# Quantity
123 ——-3
234 ——-2
.789 ——5
.543 ——2
..357——3
..246 —–2
..975 —–1
678 ——-1

Thanks.

Reply | Quote

Replies

I’m sorry, but I’m not sure what you’re trying to demonstrate. Do the periods to the left of some of the numbers represent and indentation? If so, what do they mean? Access reports can do all sorts of things and a list is the simplest form of report, but you have to explain what you want to do first.

Reply | Quote

Sorry. The dots represent the sub-assembly level. The example I gave would be the resulting BOM of a top-level assembly. Essentially, anything with sub-assemblies is a top-level assembly–but is a sub-assembly when a part of another part. The part description, cost, etc. of each part number is contained in the main component database (this database is then referred linked to for the part information). It is easy to make a BOM of any one assembly an one level. However, we are trying to achieve the many level indented/exploded view of one top level assembly’s sub-assemblies, etc.

To explain the example I gave, a part with no dots represents a part that is a direct sub-assembly of the top-level part the BOM is showing. Dots (or indention) show the respective relationship of sub-assemblies and sub-assemblies of those sub-assemblies.

Reply | Quote

Hi Robin

Don’yt know if this will get you on right track.

This came from Access Web, don’t have the url at my fingertips, possibly another lounger can get you there, or try google for bom.mdb.

HTH

John

—Posted by Michel Walsh—

————————————————————
Queries: BOM, with Joe Celko Nested Sets
————————————————————

As presented in “Joe Celko’s SQL for smarties” and discussed in many newsgroups, the nested set solutions are up to 1000 times faster than their equivalent methods (mainly based on cursors or recordsets).

While the standard discussions turn around maintenance (adding and deletion of nodes in the “graph”), there was no example about the BOM problem based on that kind of solutions, at least, up to now, since now, you can find one, in the Jet-Access 2000 zipped database included here. That database has one table, the nested set (we assume you are familar with the notion), a single form showing graphically the tree represented in the table, and one query returning the list of the required elements, and in which quantity, to make an arbitrary item described in the nested set.

As you can see by yourself, there is no VBA, no recursion, only plain SQL is used to solve that kind of problem. The query can surely be re-used for any nested-set, not just for the one given as example.

Celko-BOM.zip
(Access 2000 database)

Reply | Quote

Here the link Support4John is speaking about.

Reply | Quote

I am getting an “unrecognized database format” message when I try to open the database in this link. I am using Access 97, SR2. Please advise.

Reply | Quote

Attached the database converted to 97 format.

Reply | Quote

Thank you. I think that this is, at a glance, what I need. I’ll be examining it in detail later today and tomorrow. Thanks again.

Reply | Quote

i download it and work on it for few days but i do not know how to write a small code (SQL or VBA ) to count from left and right along the node.

Can anybody help?

Reply | Quote