• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5382
  • Last Modified:

Multi level bill of material explosion queries

Hello everybody
I'm new to ms access vba, i need to write a vba to explode the part lists in the bill of material.(multi level BOM) I've heard of recursive BOM query. but i don't really understand it well. the BOM table has the following  three fields.
ParentID   ChildID and QuantityPer. how do i go by solving the problem on hand. please help
0
Mike31
Asked:
Mike31
  • 3
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I've attached 3 sample files. The first two are SQL solutions. The third is a VBA based solution. Holler if any questions.
JimD.

NestedBOM.zip
NestedBOM-nico5038.zip
BOMSample.zip
0
 
Mike31Author Commented:
Thanks for the response, but i have one more question. my table consists more than 95 K Parent items. i want to explode the whole table once. sort of run a query to explode all parts at once.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Thanks for the response, but i have one more question. my table consists more than 95 K Parent items. i want to explode the whole table once. sort of run a query to explode all parts at once.>>
 Why?   I can't think of any operation that would require that.  If your trying to build a where used index, you only need to explode each BOM one at a time and buildl the index as you go along.  Costing is no different.
  Exploding 95K parent items all at the same time could be extremely resource intensive depending on the depth of the BOMs involved.
JimD.
 
 
0
 
Mike31Author Commented:
Hi JD
sorry for the misunderstanding, I have over 95 K Parent Items that needs to be exploded. that is what i meant. instead of exploding item by item, i need to run a query to explode the BOM table which contains the 95 k items and their children, into the the tblExplodedBom as demonstrated in the access vba sample database.
thanks
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You'd write another procedure then to:
1. Open the parent table
2. Loop through all 95K records
3. Call ExplodeBOM() for each record.
4. Read tblExplodedBOM and do something with it (like build a where used index).
ExplodeBOM() is just the base procdure to expload a single BOM, but you can use that from other procedures to eiasly build up all kinds of functionaility (ie. costing, printing an indented BOM report, where used, etc).
JimD.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now