Yes, the vendor id is different from the primary key. It is an autonum. The
old vendor numbers have to be maintained for historical reasons - all
transactions (up to two years worth) linked to them need to be accessable for
queries.

"BruceM via AccessMonster.com" wrote:

Is the vendor number different than the primary key? If so, is there a
reason you can't just change the second (presumably visible) vendor number
without creating a new record, or do you need to maintain the old vendor
number for historical reasons?

Bird Byte wrote:
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

.