• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5187
  • 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)PresidentCommented:
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)PresidentCommented:
<<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)PresidentCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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