Parts made up of parts made up of parts

[Helpless or Hapless, but Never Hopeless]

Hi,
I have tried searching on this board for an applicable answer but was unable
to find one that would suit my needs.
I'm trying to create a database that has quantities of parts that belong to
components. Each part has a name and a part number. Each of these parts may
be made up of other parts, which may consist of other parts, which may
consist of other parts, i.e., you could have a screw as a part alone, or a
screw as part of a plate which is part of a breaker which is part of a
computer. (Please excuse the analogy, but I hope the point is made.)

Here's how I started:
tblComponents, with fields
Component (links to Components table, but that's another story)
(1)Part (PK)
PartID

tblPartInfo, with fields
Color
PartNo
(2) Part (PK)

tbl PartLink, with fields
(3)Part (PK)
ConsistsOf (PK)

In the relationships window, I created a copy of tblPartInfo. I have a 1toM
relationship between (1)Part and (2)Part, (2)Part and (3)Part, and ConsistsOf
and (3)Part.

Something isn't right here, I know, but I don't know how to fix it. And when
I go to set up forms, am I going to be completely screwed?

 

[mnature]

A simplistic view would be to have three tables. One will be of Items, and
Items can be Parts and/or Assemblies.

tbl_Items
ItemID (PK)
ItemName
ItemNumber
ItemDescription

tbl_Assemblies
AssemblyID (PK)
ItemID (FK)
AssemblyDescription

tbl_Parts
PartID (PK)
AssemblyID (FK)
ItemID (FK)
UsageQuantity
UnitOfMeasurement

In the Relationships window, ItemID in Items table will connect with both
Parts and Assemblies tables, and AssemblyID will connect between Parts and
Assemblies tables. In this way, an item can be an assembly and/or part of an
assembly. By using a form and subform, you can bring up an assembly, and see
which parts (and how many) go into it.

However, this is a very simple way of looking at this type of problem. If
you look at
http://www.dbamanufacturing.com/ and download their free demo, they have a
very elegant way of looking at BOM (bill of material). However, their
product is a canned program, so is not available as an Access template for
you to use.

 

[Helpless or Hapless, but Never Hopeless]

I know how hierarchies work, I just don't know how to show them in Access and
the sample sent did not seem to answer my question.