Parts made up of parts made up of parts

[Guest]

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?

 

[Jamie Collins]

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

'Bill of materials', from Wikipedia:
http://en.wikipedia.org/wiki/Bill_of_materials

'Graphs, Trees, and Hierarchies' by Joe Celko:
http://www.dbazine.com/ofinterest/oi-articles/celko24

'BOM, with Joe Celko Nested Sets' from Access MVPs:
http://www.mvps.org/access/queries/qry0023.htm

Jamie.

--

 

[Guest]

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.

 

[Guest]

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.

 

[Jamie Collins]

I know how hierarchies work

I apologize if I offended you. I assumed that someone who knew they had
a BOM or hierarchy on their hands would namecheck it.

I just don't know how to show them in Access

Asking how to model hierarchies in a SQL product is a bit beyond the
scope of a newsgroup thread, IMO. Why, the issues could fill a whole
book! Thankfully, there is such a book:

Joe Celko's Trees and Hierarchies in SQL for Smarties
http://www.amazon.com/gp/product/1558609202/

I read it a couple of weeks ago and highly recommend it. The author
clearly prefers a nested sets approach over the more commonly
encountered adjacency list approach (procedural and denormalized in
nature) yet still provides lots of useful routines for both.

Jamie.

--