Alternate parts and releated parts from 1 table

[odb2012]

Hi,

I have a table with an ID and PartNumber field.

Parts tbl
PartNumID PK(Auto)


PartNumber

Here’s some example data:
1 M24308/2-2F
2 M24308/4-2F
3 ORD9F0000
4 ORD9M0000
5 D90000VLO
6 D90000JOO

How would I design the table to allow me to define alternatepart numbers and also related parts? For example:
Record 1 is the primary part and record 3 is the alternatepart.
Record 2 is the primary part and record 4 is the alternatepart.
Records 5 and 6 are related parts to records 1, 2, 3 and 4.

I have a form that allows the user to select a part and displaythe part data. But I also want the alternate parts and related parts displayedon the form, maybe in a subform with tabs. I haven’t gotten there yet because Ican’t determine the table design.

Any ideas would be great.

Thanks, Paul

[Robeen]

First, a couple of questions:
Do Alternate Parts and Related Parts need to exist in the Parts table?

Or are Alternate Parts and Related Parts only relevant in the context of the 'Primary' parts to which they are either 'alternate' or 'related'?

One possible way to do what you want is:
1. Create a separate table in which you have a PartNumber field - [that ties to the PartNumber field in your Parts table] and a 'RelatedPart' field, and
2. Create a separate table in which you have a PartNumber field - [that ties to the PartNumber field in your Parts table] and an 'AlternatePart' field.

[odb2012]

Robeen,

Alternate, related and primary parts are all required in "a" table.All parts have separate locations within inventory and have separatecertification paper work and must be stored separate.
Primary and alternate parts are actually manufacture and supplier part numbersM24308/2-2F is a mill spec part number and ORD9F0000 is a supplier part number.
Related parts are parts that can be used with each other but must be storedseparate.
Is this thestructure for your possible way?

AlternatePart tbl
PartNumber
Alternatepart

And

Relatedpart tbl
PartNumber
RelatedPart

Would the data inthe table be the same data as in the Part tbl?

[Robeen]

In your Parts Table - you would not record the Related and Alternate Parts for each PartNumber.

In your AlternatePart Table - every PartNumber that has an AlternatePart will have to be entered.
Same for RelatedPart Table.

That way - if you want to find all the Related and Alternate parts for any given Part - you can query those two tables to get them.