You haven't described what you need to do...

If you just need to keep VendorA connected to his/her "jobs/items/...", use
the AutonumberID, not whatever "name/number" the vendor uses for him/herself
this week.

If you need to keep a history so you know that last year VendorA went by
"12345" but this year is going by "98765", you'll need to keep a history
table...

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Bird Byte" wrote in message
...
Hello. I have an instance that I'd like some input-suggestions on if
anyone
would be so kind. We have a vendor table with each vendor ID being unique.
One problem is, some vendor numbers have changed so there is a current #
and
an old #. Because of the multiple vendor #s the primary key is an auto #.
The
main dilemma I'm having with the design is that at least one vendor (maybe
a
few more but I'm not sure yet) has two old numbers and one new/current #.
There are hundreds upon hundreds of vendors, and I'm wondering if a third
vendor # field is the way to go. It seems like a cumbersome solution,
especially since it's (apparently) only one vendor. On the other hand,
although it's unlikely, the situation might come up again and I want to be
ready for it even if it's 5 years down the road.

I'm grateful for any ideas.