[AccessD] Finding the levels in a bill of material

 I'm e-mailing you off-list three different approaches.  One is a VBA based
solution by myself, one is a SQL solution from Nico Altnick, and the other
SQL based by Joe Cleko.

 There's also an article on EE:

Expanding a Hierarchical Data Structure
http://rdsrc.us/IEdCko

  which I gave some input on.  It's similar to the VBA solution I'm
e-mailing, but slightly different.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ewaldt at gdls.com
Sent: Thursday, May 22, 2014 07:46 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Finding the levels in a bill of material

In a simplified bill of material (BOM), let's say I have (first line 
contains field names):

PartNumber,     Parent  , Level
001, (null), 0
002, 001, 1
003, 001, 1
004, 003, 2
005, 001, 1
006, 005, 2
007, 005, 2
008, 007, 3
002, 007, 3
009, 005, 2
010, 001, 1

I put the levels in to show what I actually need to find. In other words, 
I have the part number and the parent's part number, but don't have the 
level. so pretend the levels are all blank. In the real tables, there 
could be over 20 levels and 20,000 records.

Since it's zero-based, the first is level 0, of course. Any parts showing 
the first one as the parent logically will be level 1. Those are the easy 
ones. 

Part numbers can appear more than once and at different levels (not as a 
descendent of themselves, of course). Note that part number 002 shows up 
as a child of 001, and also as a child of 007 (with all that messing 
around, you have to figure Bond has some kids somewhere).

BTW, each also has an ID number (lngID), assigned in the order the parts 
are presented.

Does anyone have a good algorithm to find the Level for these?

Thanks in advance for any and all help.

Tom Ewald
Mass Properties
General Dynamics Land Systems
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com