How to handle changed product number?

[kagoodwin13]

I'm getting to the near end of my database project. It stores rates for logistics for the past 10 years. I've run into a major problem though - over the years, the same product has changed ID numbers. This causes a problem when I try to marry it up with a table storing shipment volume by product ID number - the tables will only link if the product number matches what is in the volume table, and searches for the new product number get dropped.



Further complicating the matter, sometimes the newer product number was used previously on a different product. There was no "assigned" number for a product - whatever number was available was used.

Is there a generally-accepted way to deal with this? I'd hate to lose the ability to marry product info to shipment volume.

[June7]

You want to capture data from the last 10 years into this new db?

Are these product ID numbers strictly for internal use, not derived from some outside source such as suppliers?

The ID number is manually assigned to product record? This ID is not uniquely associated with a specific product as it can be re-used. You cannot use it for pk/fk linking and searching. Will need a unique ID for that. An autonumber field can serve. That or fix your data and standardize the product numbering to not allow duplicate use of the ID. If product must be given a new ID then that will mean another record in products table and the old product record 'deactivated' and not made available for selection in comboboxes, etc.