473,715 Members | 2,234 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Recursive code to create indented bill of material

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPart No
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list the
parts and assemblies with their respective parts in a hierarchical list. I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found exactly
what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.
Nov 13 '05 #1
20 18863
Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to
identify how deep the tree is and then build a table with as many columns as
there are levels in the tree. *Most* trees tend to be between 3 and 7
levels meaning most hierarchies could be mapped in a table with 7 columns.
Anyway, building your report would mean iterating through the columns of
this table from a beginning point. As an example, let's say CD jewel cases
are part of the finished good, Virtual PC. The jewel case is near the
bottom of the hierarchy because it consists of four components and contains
a CD, which is at the bottom of the hierarchy. The jewel case goes into an
interior box that holds it in place which goes into an outer box. Anyway,
by my count we've got four levels meaning we need four colums to map every
combination of CD, jewel case & boxes. Virtual PC is part #vpc_42005 in my
example. So, I find the row in the first column that has my part number and
then loop through my columns to find component parts. Done, and no
recursion needed.
--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS"

"Gary Manigian" <ga***@designco ncepts.net> wrote in message
news:30******** ********@fe10.l ga...
I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPart No
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list
the parts and assemblies with their respective parts in a hierarchical
list. I want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found
exactly what I an looking for. I thought I would create a list of parts
that contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

Nov 13 '05 #2
read these,,,
http://www.mvps.org/access/queries/qry0023.htm
http://www.mvps.org/access/modules/mdl0027.htm

and if you really want a brain teaser, read Joe Celko's "SQL for
Smarties". Not being a smarty, I guess I'm not qualified...

Nov 13 '05 #4
"Gary Manigian" <ga***@designco ncepts.net> wrote in message
news:30******** ********@fe10.l ga...
I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPart No
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list the parts and assemblies with their respective parts in a hierarchical list. I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found exactly what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.


For a TRULY recursive BOM, there needs to be some revision to the table
structure, for the sake of normalization, like so:

tblBOM: (Stores all part Keys, Part Numbers and Descriptions, no need for
this data in BOTH tables)
lngBOMID (PK)
strPartNo
strDescription

tblBOMDetail: (Stores only the parent-child relationships and the qty-per)
lngBOMDetailID (PK)
lngBOMID (FK)
intQuantity

Then, paste the following code into a new module:
'---------------<< begin code >>---------------
Public Sub EnumBOM(Optiona l llc As Long = 0, Optional PK As Long)

Dim rst As Recordset
Dim x As Long

If llc Then
Set rst = CurrentDb.OpenR ecordset("SELEC T DISTINCT tblBOM.*, " _
& "IsNull(tblBOMD etail2.lngBOMID ) AS FK " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID =tblBOMDetail.l ngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID =tblBOMDetail2. lngBOMDetailID " _
& "WHERE tblBOMDetail.ln gBOMDetailID=" & PK & ";")
Else
Set rst = CurrentDb.OpenR ecordset("SELEC T DISTINCT tblBOM.*, " _
& "IsNull(tblBOMD etail2.lngBOMID ) AS FK " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID =tblBOMDetail.l ngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID =tblBOMDetail2. lngBOMDetailID " _
& "WHERE tblBOMDetail.ln gBOMDetailID Is Null;")
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescript ion
If IsNull(rst!FK) = False Then EnumBOM llc + 1, rst!lngBOMID
rst.MoveNext
Loop

rst.Close

End Sub

'----------------<< end code >>----------------

I tested the above with the following sample data in the tables:
tblBOM:
lngBOMID strPartNo strDescription
1 AssyNo1 Assembly No. 1
2 AssyNo2 Assembly No. 2
3 Comp1 Component 1
4 Comp2 Component 2
5 Comp3 Component 3
6 SubAssy1 Sub-Assembly 1
7 SubAssy2 Sub-Assembly 2
8 SubAssy3 Sub-Assembly 3

tblBOMDetail:
lngBOMDetailID lngBOMID intQuantity
1 3 1
7 3 1
1 4 1
2 4 1
7 4 1
2 5 1
6 5 1
8 5 1
1 6 1
2 7 1
7 8 1

With the tables populated with the above data, running EnumBOM produced this
result:
AssyNo1: Assembly No. 1
Comp1: Component 1
Comp2: Component 2
SubAssy1: Sub-Assembly 1
Comp3: Component 3
AssyNo2: Assembly No. 2
Comp2: Component 2
Comp3: Component 3
SubAssy2: Sub-Assembly 2
Comp1: Component 1
Comp2: Component 2
SubAssy3: Sub-Assembly 3
Comp3: Component 3

If you study the above information carefully, you will see how it works. If
you would like a more detailed elaboration, by all means, ask.
Nov 13 '05 #5
On Wed, 13 Apr 2005 21:43:25 -0400, "Alan Webb" <kn*****@hotmai l.com> wrote:
Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to


What version of Access was this? I wrote code to deal with BOMs using
recursive code a while back in Access 2000, and it worked fine at 12 levels or
so.

You do have to make sure there are no recursive tree branches! It's harder to
prevent this than you might think, and the user error is not as hard to make
as you might think.
Nov 13 '05 #6
Back to 10 years.... Writing a recursive BOM listing in a language
not supporting recursive (like BASICA or RPG/400) would be painfull.
Write it in BASICA, then re-write in RPG/400.

Every local variables should put to stack implemented by array.
Nov 13 '05 #7
Steve,
It was Access 2000 on a complex bill of materials for a fulfillment house
that did finish packaging of software. It was at Schwab where I saw the
table with each column of the hierarchy and I thought it was cool.
--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS"

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:m9******** *************** *********@4ax.c om...
On Wed, 13 Apr 2005 21:43:25 -0400, "Alan Webb" <kn*****@hotmai l.com>
wrote:
Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to


What version of Access was this? I wrote code to deal with BOMs using
recursive code a while back in Access 2000, and it worked fine at 12
levels or
so.

You do have to make sure there are no recursive tree branches! It's
harder to
prevent this than you might think, and the user error is not as hard to
make
as you might think.

Nov 13 '05 #8
If its any help I wrote a database for recipes (Actually Flavours for a
flavour making company). Each flavour consisted of ingredients and
optionally other flavours. E.G A cheese and onion flavour consisted of a
cheese flavour + oil of onion etc. The cheese flavour consists of a cheese,
a chemical etc
There was no limit on how name flavours were nested

Phil
"Gary Manigian" <ga***@designco ncepts.net> wrote in message
news:30******** ********@fe10.l ga...
I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPart No
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list
the parts and assemblies with their respective parts in a hierarchical
list. I want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found
exactly what I an looking for. I thought I would create a list of parts
that contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

Nov 13 '05 #9
"Gary Manigian" <ga***@designco ncepts.net> wrote in
news:30******** ********@fe10.l ga:
I have 2 tables, one-to-many, that contain bills of
material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPart No
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I
need to list the parts and assemblies with their respective
parts in a hierarchical list. I want to do this recursively in
VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't
found exactly what I an looking for. I thought I would create
a list of parts that contained the indent level and from there
create the report.

What I am really stuck on is creating the recursive routine.
Can someone please get me started.

Thanks in advance.


First I'd make a couple of minor changes to your structure.It's
not normalized.

TBL_PARTNo
str_PartNo, PK
Str_Description
str_UM

Tbl_ProductStru cture
str_ChildPartNo FK
str_ParentPartN o FK
currQtyPer (you may want to handle 0.5 tube of glue)

pk is the two FKs

The way I approached the problem was not to go recursively, but
to create an array of datasets, writing out to a temporary table.
a simple do until EOF() loop walks the first recordset, tests if
the current part is a parent, and opens the new recordset in the
next Indenture Level element of the array if it is. If it's not a
parent just move to the next record

Hit EOF() and decrement the Indenture level, pointing back to the
parent.

Loop.

It creates a 4000 row table in about 20 seconds, using SQL_Server
tables,

I also have a string array where I store the parent partNOs and
walk that to test for inbreeding (item is a child or grandchild
of itself) which leads to infinite depth BOMs.
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
2716
by: TaeKyon | last post by:
I'm a python newbie; here are a few questions relative to a problem I'm trying to solve; I'm wandering if python is the best instrument or if awk or a mix of bash and sed would be better: 1) how would I get recursively descend through all files in all subdirectories of the one in which my script is called ? 2) each file examined should be edited; IF a string of this type is found
2
7029
by: Perttu Pulkkinen | last post by:
I need to find toplevel image categories and a) number of images directly in them and b) number of subcategories directly in those topcategories. In toplevel image categories "icat_parent_id IS NULL". Below is also current erraneous draft but it has ambigous filed name problem. CREATE TABLE icategories ( icat_id int(11) NOT NULL auto_increment, icat_parent_id int(11) default NULL, icat_name char(100) default NULL, PRIMARY KEY ...
1
1608
by: None | last post by:
Hello all, I have a 97 database that I have been trying to consolidate code. Most of my forms use visual aids for the user to guide them along with the data entry. For example, I will make the background color green to let the user this is where you should be entering data, red if the data has been validated and accepted. I was wondering, with the following code is there a way to create a function where I can LOOP? That way if I need...
25
2826
by: Mike MacSween | last post by:
Regular viewers may want to turn off now. This will be an orchestral management system. Musicians and other staff being booked/paid for jobs. A job may contain other jobs, e.g: World Tour contains US leg and Europe leg (and others) US leg contains State tours (and others)
9
13207
by: Bill Borg | last post by:
Hello, I call a function recursively to find an item that exists *anywhere* down the chain. Let's say I find it five layers deep. Now I've got what I need and want to break out of that whole stack and continue execution at the point of the initial call. Is that possible? Thanks, Bill
5
5032
by: purushneel | last post by:
Hi, I work primarily on Oracle databases. I am trying to convert a recursive stored procedure written in Oracle to DB2. Does DB2 UDB v8.2 (Windows/AIX) supports recursive stored procedures ?? After some research, I found out that to call recursively in DB2, the stored procedure should be CALLed using dynamic SQL. I am not sure whether it is the right way. Am I missing something ?? Please let me know...
1
4368
by: ozcanseker | last post by:
I am trying to write a product-row material cost program. Every product consists of row materials. When I sum up cost of row materials of each product I can find cost of products. But when the row material of the a product is a row material again the my solution does not work. My table is like that: Product Row material p1 r1 p1 r2 p1 r3 p2 r4 p2 p1
4
1878
by: Ted | last post by:
Understand, I have developed a number of applications using RDBMS, including MySQL, PostgreSQL and MS Access, but this is my first experience with MS SQL. I'd bet my bottom dollar that MS SQL supports what I need, but I just haven't found where it is explained in any detail in the documentation I have. The pages I have found strike me as a little too terse for my needs. In MySQL, I used statements like: PRIMARY KEY (`ic_contact_id`),...
41
3366
by: Harry | last post by:
Hi all, 1)I need your help to solve a problem. I have a function whose prototype is int reclen(char *) This function has to find the length of the string passed to it.But the conditions are that no local variable or global variable should be used.I have to use recursive functions.
0
8820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9332
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9195
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9100
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9044
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7969
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6645
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4738
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3174
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.