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
Mike31Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.