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

    Thanks Shamil! - for kindly responding. I have downloaded the module referred by you and shall study it further.

    Also, thanks for sharing the historical background. Remarkable indeed!

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Shamil Salakhetdinov 
  To: 'Access Developers discussion and problem solving' 
  Sent: Saturday, June 19, 2010 14:23
  Subject: Re: [AccessD] BOM - Table Structure For Handling Common PartsInVarious 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
  ------------