[AccessD] BOM - Table Structure For Handling Common Parts InVarious Assemblies/Sub-Assemblies.

Hi A.D.,

You have to introduce "relationship" M:N table I suppose - have a look at
this sample code:

Representing a tree structure in an RDBMS system
http://smsconsulting.spb.ru/shamil_s/topics/treetrav.htm 

that code works if you copy and paste it into a code module - I have just
run it in Access 2003...

It's rather old (Tue, 10 Jan 95 04:33:28 +0300) but it has a complete code
solution and it could be helpful there I suppose:

- it has two tables [Unit] and [Units Hierarchy] to represent hierarchical
structure;
- it has [Units Traversal] table to keep BOM structure traversal results -
that table would be "virtual" of course for large BOM structures...

Here is a sample BOM traversal result:

1              3900 [ Assembly ]
| 
+-2            0 [ Assembly ]
| | 
| +-3          100 * Base unit *
| | 
| *-4          0 * Base unit *
|   | 
|   +-7        0 [ Assembly ]
|   | | 
|   | +-8      100 * Base unit *
|   | | 
|   | *-9      500 * Base unit *
|   | 
|   +-9        200 * Base unit *
|   | 
|   *-10       1900 * Base unit *
| 
+-5            300 * Base unit *
| 
*-6            0 [ Assembly ]
  | 
  +-3          100 * Base unit *
  | 
  +-4          0 * Base unit *
  | | 
  | +-7        0 [ Assembly ]
  | | | 
  | | +-8      100 * Base unit *
  | | | 
  | | *-9      500 * Base unit *
  | | 
  | +-9        200 * Base unit *
  | | 
  | *-10       900 * Base unit *
  | 
  +-7          0 [ Assembly ]
  | | 
  | +-8        100 * Base unit *
  | | 
  | *-9        500 * Base unit *
  | 
  *-10         1700 * Base unit *

TotPrice = 3900

Given the following BOM loading code snippet:

LoadUnitsHierarchyRow u, 1, 2
 LoadUnitsHierarchyRow u, 1, 5
 LoadUnitsHierarchyRow u, 1, 6

 LoadUnitsHierarchyRow u, 2, 3
 LoadUnitsHierarchyRow u, 2, 4
 LoadUnitsHierarchyRow u, 6, 7
 LoadUnitsHierarchyRow u, 6, 10
 LoadUnitsHierarchyRow u, 7, 8
 LoadUnitsHierarchyRow u, 7, 9
 
 LoadUnitsHierarchyRow u, 6, 3
 LoadUnitsHierarchyRow u, 6, 4
 
 LoadUnitsHierarchyRow u, 4, 7
 LoadUnitsHierarchyRow u, 4, 10
 LoadUnitsHierarchyRow u, 4, 9

Thank you.

-- Shamil

P.S. Note: The code was originally written in Access 2.0 when using blanks
in table names, using DoCmd. , not using LRNC (it didn't exist/wasn't
available here that time) etc. was not yet considered as a "mauvais tone"
coding style...

Funny enough but nowadays when LRNC got depreciated by MS VB.NET coding
guidelines that 15 years old code looks not so bad...


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D. Tejpal
Sent: Friday, June 18, 2010 10:02 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] BOM - Table Structure For Handling Common Parts InVarious
Assemblies/Sub-Assemblies.

    Typical fields for a table having hierarchical list of assemblies and
parts are observed to include:  ItemID, ParentID, Description, Qty

    With this arrangement, the complete hierarchical chain from each topmost
item (having no immediate parent) downward can be worked out right upto the
lowest part (having no child).

    In situations where a part with same ItemID is needed for more than one
Item chain (i.e. more than one top parent), the above table structure might
not prove adequate. 

    Any suggestions - while retaining its role as a self contained table ?

Best wishes,
A.D. Tejpal
------------
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com