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

    Thanks Drew! That no doubt has its advantages.

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

  ----- Original Message ----- 
  From: Drew Wutka 
  To: Access Developers discussion and problem solving 
  Sent: Saturday, June 19, 2010 01:00
  Subject: Re: [AccessD] BOM - Table Structure For Handling Common PartsInVarious Assemblies/Sub-Assemblies.


  I'd say the best approach would just be to have a separate ID table.
  ItemID and ChildID (which be the itemID of any child parts).  That gives
  you a one too many relationship, and it doesn't matter if the same part
  is a child of multiple parts.

  How to build a SQL view of this....well, honestly, that's not my forte.
  I tend to build classes in code (VB, asp, and now .Net).  In those
  cases, this type of table structure is easily broken down with the
  appropriate class structure.

  Drew

  -----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 1: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
  ------------