Linking Part Numbers

[Guest]

In my database I key in data for what is run on a line. What part is loaded,
and the number of that part. The part gets enameled (sprayed) and when it
comes off the line it has a different part number depending on the color.

Part # Loaded: 32476598
Amount Loaded: 300
Part # Unloaded: 32476601
Amount Good: 295
Number Bad: (Black Specks) 2
(Light Spray) 3

There are 3 tables:
tblLoad (Date; Part #; #Loaded; Color; Shift)
tblGood (Date; Part #; #Good; Color; Shift)
tblBad (Date; Part #; TypeReject; Color; Shift)

How do I link the part numbers so I can get the % Yield with the part number
changing once it is coated. Could I create a table with LoadPart#;
FinishPart#; and Color and use it to link the numbers or is there another
way? The finished result should show what part was loaded, total loaded,
date, shift, the different rejects, and percent good.

I have used a database for this for some time but our part number going on
was always the same coming off. Now a new numbering system is being used that
changes the part number after it is being coated a specific color (which is
known whn it is loaded).

 

[(PeteCresswell)]

Per dbaggett:

How do I link the part numbers so I can get the % Yield with the part number
changing once it is coated. Could I create a table with LoadPart#;
FinishPart#; and Color and use it to link the numbers or is there another
way? The finished result should show what part was loaded, total loaded,
date, shift, the different rejects, and percent good.

I have used a database for this for some time but our part number going on
was always the same coming off. Now a new numbering system is being used that
changes the part number after it is being coated a specific color (which is
known whn it is loaded).

Without a lot of deep thought, my reaction is that a part is a
part - and it should have it's own PK throughout it's life.

In my little world, that PK would be something like an AutoNumber
field. i.e. a random dumb number with no significance whatsoever
except that it is uniquely associated with a particular hunk or
assemblage of something.

Then it would have as properties:
----------------------------------------------------------------
- PartType: something that uniquely identifies it's physical
shape.

- CoatingColor: which tells what the color is (maybe "0" for
none)
----------------------------------------------------------------

PartNumber could then be derived on-the-fly by a function that
considers PartType CoatingColor.

That would also put you in a better position for situations like
numbers changing based not only on color, but type of coating
(CoatingType?)... like paint vs anodization vs whatever.

Also, this sounds like it could more complicated if/when
assemblies of parts/subparts come into the picture.

 

[Bob Quintal]

In my database I key in data for what is run on a line. What part
is loaded, and the number of that part. The part gets enameled
(sprayed) and when it comes off the line it has a different part
number depending on the color.

Part # Loaded: 32476598
Amount Loaded: 300
Part # Unloaded: 32476601
Amount Good: 295
Number Bad: (Black Specks) 2
(Light Spray) 3

There are 3 tables:
tblLoad (Date; Part #; #Loaded; Color; Shift)
tblGood (Date; Part #; #Good; Color; Shift)
tblBad (Date; Part #; TypeReject; Color; Shift)

How do I link the part numbers so I can get the % Yield with the
part number changing once it is coated. Could I create a table
with LoadPart#; FinishPart#; and Color and use it to link the
numbers or is there another way? The finished result should show
what part was loaded, total loaded, date, shift, the different
rejects, and percent good.

I have used a database for this for some time but our part number
going on was always the same coming off. Now a new numbering
system is being used that changes the part number after it is
being coated a specific color (which is known whn it is loaded).

What you are describing is a standard Bill of Material application,
where a parent part number is made from one or more parts, which
themselves may be made from lower-level parts

To do this effectively, you want a table that defines each part,
with the part number as primary key, description, and other
attributes like unit of measure. and a second table that contains 3
fields, Parent_Part, Child_Part, qty

You also want a production table, which is a combination of your
three tables load, good and bad,
This would contain a production lot number, in your case parent
part, date and shift, or any lot specific number. QtyIn, qtyOut, You
don't need to store qtybad, because that is always calculated as in
- out. You may include typereject in this table, but you may want to
split this field and the lot number to another table, if you want to
track multiple reasons for rejection on a single lot.

You use relations to get data from the related tables when required.