relookup with multiple relationships

[jim schlutnick]

I am having a problem with a inventory file matching part #'s.

I have a master list of master part #s (approx 800,000 records).

this file contains a manufacturers part # and a cross reference to my part #.

I have an addional inventory file which contains up to 3 alternate part #'s (alt1, alt2, alt3) and a cross reference to my part #.

My problem is when a get an order from a customer. they may give me either the master part #, alt#1, alt#2 or alt#3.

i am trying to relookup my part # into the list they sent me. the only way i can see is to create 4 relashionships. do a relookup, change the defination, do another lookup, etc... al manually.

is there a better way to do this?

I need to maintain the integrety of the masterfile as it is used for otherpurposes. i want to avoid adding too many additional fields as it is already 300MB

[Kurt Knippel]

While you may not want to hear this, I think that you are making this way too complicated.

You have YOUR PART NUMBER and you have OTHERS PART NUMBER. Drop the whole Master, manufacturer, alt1, alt2, alt3 system. Just make those a designation or another field in the record.

Keep YOUR PARTS in one file, and joined to it is a file for PART NUMBER MARCHING. In this file are all the various different part number, what type of part number they are, and what corresponding YOUR PART NUMBER they are matched with.

Then you only need to look in one place and in one field to determine what you part number is.