Recursive BOM

[pascal@zembra.net]

Hello All,

I'm a middle-low level user of MS Access 2000. I'm trying to find a
solution to what appears to be a common problem. By browsing the net
and the groups, I've seen several posts and solutions to that problem
but I've not been able to find a ready to use query or module directly
working into MS Access.

The problem is the standard BOM problem.
I have a BOM structure setup in 2 tables:

tblParts:
PartID PK Number
Part Code Text
Part Description Text

tblBOM:
Parent Code Text
Child Code Text
QTY Number

And of course we can have an unlimited number of level in the BOMs.

Here's a 3 levels exemple:

tblPart
PartID Part Code Part Description
1 Finish1 Finished Part 1
2 Finish2 Finished Part 2
3 SemiFinished1 Semi Finished Part 1
4 SemiFinished2 Semi Finished Part 2
5 Component1 Component 1
6 Component2 Component 2
7 Component3 Component 3
8 Component4 Component 4

tblBOM
Parent Code Child Code QTY
SemiFinished1 Component1 1
SemiFinished2 Component1 1
SemiFinished1 Component2 3
Finish1 Component3 2
SemiFinished2 Component3 3
Finish2 Component4 3
Finish1 SemiFinished1 1
Finish2 SemiFinished1 4
Finish2 SemiFinished2 1

I'm trying to have a query that would give me for a given part, the
complete list of components whatever the number of levels in the BOM.

For exemple
Finish1 Component3 2
Finish1 Component2 3
Finish1 Component1 1

I've created a database with the 2 tables available at:
http://www.zembra.net/BOM.mdb

Any help to develop/integrate this query would be more than greatly
appreciated.

Thanks, Pascal

[rquintal@sympatico.ca]

Re: Recursive BOM

Hello Pascal,

Access won't let you recurse a query, but you can wrote some code that
sets up an array of parts lists recordsets, then processes each single
parts list in a loop, opening the recordset for the relevant lower
level as it finds it.. The quantity for each entry is the product, not
the sum.

Once you have the full indented explosion, which looks like this
Level Item QTY QTYEXPL
0 Finish1 1 1
1 SemiFinished1 2 2
2 Component1 1 2
2 Component2 3 6
1 Component3 2 2

Just sum QTYEXPL on ITEM

[lesperancer@natpro.com]

Re: Recursive BOM

you either need to explode the bom into a temporary table from which to
report on
or you can change your structure to use 'nested sets' (lots of google
info)

pascal@zembra.n et wrote:[color=blue]
> Hello All,
>
> I'm a middle-low level user of MS Access 2000. I'm trying to find a
> solution to what appears to be a common problem. By browsing the net
> and the groups, I've seen several posts and solutions to that problem
> but I've not been able to find a ready to use query or module directly
> working into MS Access.
>
> The problem is the standard BOM problem.
> I have a BOM structure setup in 2 tables:
>
> tblParts:
> PartID PK Number
> Part Code Text
> Part Description Text
>
> tblBOM:
> Parent Code Text
> Child Code Text
> QTY Number
>
> And of course we can have an unlimited number of level in the BOMs.
>
> Here's a 3 levels exemple:
>
> tblPart
> PartID Part Code Part Description
> 1 Finish1 Finished Part 1
> 2 Finish2 Finished Part 2
> 3 SemiFinished1 Semi Finished Part 1
> 4 SemiFinished2 Semi Finished Part 2
> 5 Component1 Component 1
> 6 Component2 Component 2
> 7 Component3 Component 3
> 8 Component4 Component 4
>
> tblBOM
> Parent Code Child Code QTY
> SemiFinished1 Component1 1
> SemiFinished2 Component1 1
> SemiFinished1 Component2 3
> Finish1 Component3 2
> SemiFinished2 Component3 3
> Finish2 Component4 3
> Finish1 SemiFinished1 1
> Finish2 SemiFinished1 4
> Finish2 SemiFinished2 1
>
> I'm trying to have a query that would give me for a given part, the
> complete list of components whatever the number of levels in the BOM.
>
> For exemple
> Finish1 Component3 2
> Finish1 Component2 3
> Finish1 Component1 1
>
> I've created a database with the 2 tables available at:
> http://www.zembra.net/BOM.mdb
>
> Any help to develop/integrate this query would be more than greatly
> appreciated.
>
> Thanks, Pascal[/color]

[pascal@zembra.net]

Re: Recursive BOM

Thanks a lot, this is exactly what I wouln need but I'm clueless when
it comes to writing this VB code. Is there a place where I can find
such code or is it possible toput that code into the group ?

Pascal

[pascal@zembra.net]

Re: Recursive BOM

Thanks, I explored the nested sets solution, I found all the info
on-line including a sample mdb file but this won't address my needs as
in my case, the same component or semi-finished can be used in several
finished or semi-finished products....


lesperancer@nat pro.com wrote:[color=blue]
> you either need to explode the bom into a temporary table from which to
> report on
> or you can change your structure to use 'nested sets' (lots of google
> info)
>
> pascal@zembra.n et wrote:[color=green]
> > Hello All,
> >
> > I'm a middle-low level user of MS Access 2000. I'm trying to find a
> > solution to what appears to be a common problem. By browsing the net
> > and the groups, I've seen several posts and solutions to that problem
> > but I've not been able to find a ready to use query or module directly
> > working into MS Access.
> >
> > The problem is the standard BOM problem.
> > I have a BOM structure setup in 2 tables:
> >
> > tblParts:
> > PartID PK Number
> > Part Code Text
> > Part Description Text
> >
> > tblBOM:
> > Parent Code Text
> > Child Code Text
> > QTY Number
> >
> > And of course we can have an unlimited number of level in the BOMs.
> >
> > Here's a 3 levels exemple:
> >
> > tblPart
> > PartID Part Code Part Description
> > 1 Finish1 Finished Part 1
> > 2 Finish2 Finished Part 2
> > 3 SemiFinished1 Semi Finished Part 1
> > 4 SemiFinished2 Semi Finished Part 2
> > 5 Component1 Component 1
> > 6 Component2 Component 2
> > 7 Component3 Component 3
> > 8 Component4 Component 4
> >
> > tblBOM
> > Parent Code Child Code QTY
> > SemiFinished1 Component1 1
> > SemiFinished2 Component1 1
> > SemiFinished1 Component2 3
> > Finish1 Component3 2
> > SemiFinished2 Component3 3
> > Finish2 Component4 3
> > Finish1 SemiFinished1 1
> > Finish2 SemiFinished1 4
> > Finish2 SemiFinished2 1
> >
> > I'm trying to have a query that would give me for a given part, the
> > complete list of components whatever the number of levels in the BOM.
> >
> > For exemple
> > Finish1 Component3 2
> > Finish1 Component2 3
> > Finish1 Component1 1
> >
> > I've created a database with the 2 tables available at:
> > http://www.zembra.net/BOM.mdb
> >
> > Any help to develop/integrate this query would be more than greatly
> > appreciated.
> >
> > Thanks, Pascal[/color][/color]

[Bob Quintal]

Re: Recursive BOM

pascal@zembra.n et wrote in
news:1147979031 .413621.6910@j3 3g2000cwa.googl egroups.com:
[color=blue]
> Thanks a lot, this is exactly what I wouln need but I'm
> clueless when it comes to writing this VB code. Is there a
> place where I can find such code or is it possible toput that
> code into the group ?
>
> Pascal
>[/color]

Paste into a module. (watch for line wrapping)

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strSQL2 As String
Dim db As Database
Dim rsSource(9) As Recordset
Dim rsTarget As Recordset
Dim tblname As String
Dim Seqno As Long

'============== ==============
Public Sub Explode(ByVal RootItem As String)
'============== ==============
On Error GoTo Explode_Error
Const LPN = 38
Seqno = 0
'--------------------------------------------
'Create a structure to receive the data
'--------------------------------------------
tblname = "XL" & RootItem

strSQL = "CREATE TABLE [" & tblname & "] (" _
& "Seqno long," & vbNewLine _
& "LLno integer," & vbNewLine _
& "Item text(" & LPN & ")," & vbNewLine _
& "Item_Name TEXT(64)," & vbNewLine _
& "Qty double," & vbNewLine _
& "UM text(6)," & vbNewLine _
& "Qty_Expl Double," & vbNewLine _
& "SeqNHA long," & vbNewLine _
& "CONSTRAINT seqno PRIMARY KEY (seqno)" & vbNewLine _
& ");"

DoCmd.RunSQL strSQL

Set db = CurrentDb
Set rsTarget = db.OpenRecordse t(tblname)
'-------------------------------------------
' Set up source query
strSQL = "SELECT ProductStructur e.Parent_ITEM, " & vbNewLine _
& "child_Items.IT EM_KEY," & vbNewLine _
& "child_Items.IT EM_NAME," & vbNewLine _
& "ProductStructu re.Quantity," & vbNewLine _
& "ProductStructu re.UM," & vbNewLine

strSQL = strSQL & "FROM ProductStructur e INNER JOIN ITEM_Master
AS Child_Items " & vbNewLine _
& "ON (ProductStructu re.child_ITEM = child_Items.ITE M_KEY)"
& vbNewLine

strSQL = strSQL & "WHERE (ProductStructu re.Parent_ITEM) = '"

strSQL2 = "' ORDER BY Child_Items.ITE M_KEY;"



doOneRow RootItem, 0, 0, 1

Explode_exit:
rsTarget.Close
Set rsSource(9) = Nothing
Set rsTarget = Nothing
Set db = Nothing
Exit Sub

Explode_Error:
Select Case Err.Number
Case 3010 ' Table name exists
DoCmd.DeleteObj ect acTable, tblname
DoCmd.RunSQL strSQL ' repeat the call command
Resume Next
Case 3021 'no current record
Resume Next
Case Else
MsgBox "Please report this error to R Quintal ext xxxx" &
vbNewLine _
& Err.Number & " " & Err.Description , vbCritical
resume Explode_Exit
End Select
End Sub
'============== ==============
Private Sub doOneRow(ByVal currentitem As String, ByVal LLno As
Long, ByVal SeqNHA As Variant, ByVal qtyNHA as double)
'============== ==============
Dim vBkMark As Variant
Dim stCurrentRec As String
dim qtyExplode as double
Set rsSource(LLno) = db.OpenRecordse t(strSQL & currentitem &
strSQL2, dbOpenDynaset)

Do Until rsSource(LLno). EOF
If rsSource(LLno). NoMatch Then
rsSource(LLno). Close
Exit Sub
Else
Seqno = Seqno + 1
QtyExplode = rsSource(LLno)! quantity * qtyNHA
With rsTarget
.AddNew
!Seqno = Seqno
!LLno = LLno
!Item = rsSource(LLno)! item_key
!item_name = rsSource(LLno)! item_name
!Qty = rsSource(LLno)! quantity
!UM = rsSource(LLno)! UM
!qty_expl = qtyExplode
!SeqNHA = SeqNHA
.Update
End With


stCurrentRec = rsSource(LLno)! item_key
vBkMark = rsSource(LLno). Bookmark

doOneRow stCurrentRec, LLno + 1, Seqno, qtyExplode

rsSource(LLno). Bookmark = vBkMark
rsSource(LLno). MoveNext
End If
Loop

End Sub

'============== =============== ===== End of code

Modify as needed. Hope I didn't mangle too much while
transcribing.

Run from immediate window or code: Explode "Finish1"



--
Bob Quintal

PA is y I've altered my email address.

[paii, Ron]

Re: Recursive BOM

There is a query solution, it is messy and very limited in number of layers,
but it does work. Create a query linking in the BOM table for each layer of
the bill. Left Join the child to the parent part number. Use one more then
the max number of layers, if there is anything in the last column you will
need to edit the query.

SELECT tblBOM.[Part], tblBOM.Item, tblBOM.Qty, tblBOM_1.Item AS Item1,
tblBOM_1.Qty AS Qty1, tblBOM_2.Item AS Item2, tblBOM_2.Qty AS Qty2
FROM tblBOM LEFT JOIN (tblBOM AS tblBOM_1 LEFT JOIN tblBOM AS tblBOM_2 ON
tblBOM_1.Item = tblBOM_2.[Part]) ON tblBOM.Item = tblBOM_1.[Part]
WHERE (((tblBOM.[Part])="052542"));

You will end up with the following output.

PART ITEM QTY ITEM1 QTY1 ITEM2 QTY2
052542 221326 1 null null null
null
052542 056621 2 030024 3 null null
052542 056623 3 030029 2 082293 4

Sum this up in a 2nd query

PART ITEM Total QTY
052542 221326 1
052542 056621 0
052542 030024 6
052542 056623 0
052542 030029 0
052542 082293 24

As you see the 1st query will be very large, and the 2nd query will need a
complex iif statement to add up the quantity. Which is why a VB procedure
may be easer to learn and implement.


<pascal@zembra. net> wrote in message
news:1147979031 .413621.6910@j3 3g2000cwa.googl egroups.com...[color=blue]
> Thanks a lot, this is exactly what I wouln need but I'm clueless when
> it comes to writing this VB code. Is there a place where I can find
> such code or is it possible toput that code into the group ?
>
> Pascal
>[/color]

[Bob Quintal]

Re: Recursive BOM

"paii, Ron" <paii@packairin c.com> wrote in
news:BaWdnaUUUY QLrPPZRVn-jQ@athenet.net:
[color=blue]
> There is a query solution, it is messy and very limited in
> number of layers, but it does work. Create a query linking in
> the BOM table for each layer of the bill. Left Join the child
> to the parent part number. Use one more then the max number of
> layers, if there is anything in the last column you will need
> to edit the query.
>
> SELECT tblBOM.[Part], tblBOM.Item, tblBOM.Qty, tblBOM_1.Item
> AS Item1, tblBOM_1.Qty AS Qty1, tblBOM_2.Item AS Item2,
> tblBOM_2.Qty AS Qty2 FROM tblBOM LEFT JOIN (tblBOM AS tblBOM_1
> LEFT JOIN tblBOM AS tblBOM_2 ON tblBOM_1.Item =
> tblBOM_2.[Part]) ON tblBOM.Item = tblBOM_1.[Part] WHERE
> (((tblBOM.[Part])="052542"));
>[/color]
You can use nested nz() functions to return just the pn for the
lowest level part number, but you need all the non-null
quantities to multiply out.

SELECT nz(tblbom_6.par t,nz(tblbom_5.p art,nz(tblbom_4 .part,
nz(tblbom_3.par t,nz(tblbom_2.p art,nz(tblbom_1 .part,
tblbom.part)))) )) AS PART,
nz(tblbom_6.qty ,1)*nz(tblbom_5 .qty,1)*nz(tblb om_4.qty,1)*
nz(tblbom_3.qty ,1)*,nz(tblbom_ 2.qty,1)*nz(tbl bom_1.qty,1 AS
rowQTY

The second query then is simplified to a standard summation
query.

I heard today that the BOM for the Space Station is 91 levels.

No way I'm gonna write that query!
--
Bob Quintal

PA is y I've altered my email address.