Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Mentor, Ohio, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    exploded/indented bill of material (Access 97)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: exploded/indented bill of material (Access 97)

    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.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    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)

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    Here the link Support4John is speaking about.
    Francois

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    Mentor, Ohio, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    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.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    Attached the database converted to 97 format.
    Attached Files Attached Files
    Francois

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    Mentor, Ohio, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    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.

  8. #8
    Lounger
    Join Date
    Oct 2001
    Location
    Mentor, Ohio, USA
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    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.

  9. #9
    New Lounger
    Join Date
    Jun 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exploded/indented bill of material (Access 97)

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •