Bill of Material Query & Report

[Guest]

I need to write a query that provides a multi-level Bill of Material using
Access. The table is structure with the following fields:
Assembly(parent)
Component(child)
ItemSequence
QtyPer

I am currently able to write a single level Bill of Material query. I need
to set it up for about 6 levels deep. I know it can be done using a nested
query but I haven't figured it out yet. Can anyone help me with this?
Thanks in advance.
Samantha

 

[Michel Walsh]

Hi,


If the number of level is fixed, you can try (here, for a max of 3
levels):


SELECT d.who, [a].[qtyper]*.[qtyper]*[c].[qtyper]*[d].[qtyper] AS Expr1
FROM ((ParChi AS a LEFT JOIN ParChi AS b ON a.who = b.parent) LEFT JOIN
ParChi AS c ON b.who = c.parent) LEFT JOIN ParChi AS d ON c.who = d.parent
WHERE (((d.who) Is Not Null));



with


ParChi
who parent qtyper
1 1 1
2 1 4
3 1 5
4 2 3
5 4 1
6 2 3




the result is


Query10
who Expr1
5 12
3 5
2 4
1 1
6 12
4 12



which can be read a we need 1 piece 1, 4 pieces #2, ... 12 pieces #6 ( 3
for each #2 we make, and need 4 pieces #2)




For a variable number of nesting levels, it may be preferable to use a
nested set approach.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

hi,
i doubt you will do it with 1 query.
i had to do multi-level Bill of Materials up to 4 levels
deep. I had to have a query for each level which i dumped
into a temp table. i had to create a form just for BOMs
because of all the different queries that had to run which
included a delete temp table query which i had to run
before the next BOM could run. i use a select case to test
the parent item id entered in the form and run the
appropreate series of queries based on level on the id
entered.
post back if you need more info/direction.

 

[Guest]

Hi,
I tried the self-joining process, which works for up to 3 levels. When I
added the 4th level, it's too complex and keeps crashing the database.

Can you provide me with more detail/info on how you designed yours? And how
long did it take to run your report?

thank you so much! I've spent so much time on this already trying to get it
to work!
-S