Any way to set up a Cross-Reference for products table ?

[ovonel777]

Hi

I have a products table that contains approx 10,000 records. There
are many products that may be substituted for others. For example,
Maytag makes a pump for a dishwasher. But there are several other non-
Maytag companies that make replacement pumps for the Maytag.

I set up a cross reference in another database program but I don't
know how to accomplish this using Access.

Any suggestions on how to proceed would be greatly appreciated.

Thanks
Mark

 

[KARL DEWEY]

Try using a many-to-many relationship like below.
Parts ---
PartID - primary key
Name
Manf
etc.

Xref ---
XrefID - primary key
Forward - foreign key - PartID
Backward - foreign key - PartID
Remarks

In the Relationship window put the parts table twice and the Xref table
once. Create a one-to-many relation from parts to Forward and another from
parts to Backward.
Use the remaks to add things like 'Needs 10 microfarad capacitor when used
on the Magtag model 4556' or 'Need to drill out mounting holes for
Westinghouse. '

 

[KARL DEWEY]

Create a one-to-many relation from parts to Forward and another from parts
to Backward.
These are using the different parts table in the relations window.

 

[ovonel777]

Try using a many-to-many relationship like below.
Parts ---
PartID - primary key
Name
Manf
etc.

Xref ---
XrefID - primary key
Forward - foreign key - PartID
Backward - foreign key - PartID
Remarks

In the Relationship window put the parts table twice and the Xref table
once. Create a one-to-many relation from parts to Forward and another from
parts to Backward.
Use the remaks to add things like 'Needs 10 microfarad capacitor when used
on the Magtag model 4556' or 'Need to drill out mounting holes for
Westinghouse. '

Karl

Thanks.. I will ponder that one and give it a try.

Mark