Multiple id problem

[Bird Byte]

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.

 

[Fred]

Unless I'm missing something, The short answer is that the ID number should
never change for a given vendor. Yet you imply that you have changed vendor
id# for a given vendor. And so the short simplistic answer would be to
stop doing that, and, where you have already done it, pick one as your main
#, and go to any duplicates and mark the "double entered by mistake.

I'm sure that there is another aspect to this that you need to tell us about
(= why did you changed ID #s?) in which case the answer might be different.

Hope that helps a little.

 

[Jeff Boyce]

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.

 

[KARL DEWEY]

you'll need to keep a history table...
I would call it an 'Alias table.' I think a self-join relation would do
it. Add a field for AKA. Old record gets an AKA entry to new primary key
field ID.

UNTESTED UNTESTED -- May not have IIF statement setup right ---
SELECT IIF([tblVendor_1].[AKA] Is Null, [tblVendor].[ID],
[tblVendor].[AKA]) AS Vendor, etc, ...
FROM [tblVendor] LEFT JOIN [tblVendor_1] ON [tblVendor].[ID] =
[tblVendor_1].[AKA]
WHERE ........;

 

[Bird Byte]

I need to be able to track anything that has ever been done with the vendors.
I have no control over the numbers assigned to the vendors, and have to deal
with what is in place. All vendors got a new number when the company started
using a new system internally. Now the numbers rarely change, but they do
occasionally change - one example beign if a company changes it's name.
Again, it may not be the optimal scenario, but it is what I have to deal with.

I'm curious about the history table - could you describe this a little more?
2 vendor tables with the same fields, but one from one date onward and the
other previous to that same date?

I'm thinking of creating a yes/no field that flags whether the number is
current or not. I wonder though, if I'm not thinking of some scenario where
doing this (adding a flag) might cause trouble down the road. any thoughts on
that?

Thanks for any comments.

 

[BruceM via AccessMonster.com]

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]

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]

Are you saying that Vendor ID rather than the autonumber field is the linking
field?

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.

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

[quoted text clipped - 14 lines]

 

[Bird Byte]

Sorry for not being clear. The primary key is an auto number, and the linking
field. Lets say Vendor_ID for the PK (autonumber), and VendorNumber for the
number assigned to the vendor.

Are you saying that Vendor ID rather than the autonumber field is the linking
field?

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.

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

[quoted text clipped - 14 lines]

I'm grateful for any ideas.

 

[BruceM via AccessMonster.com]

If Vendor_ID is the linking field in the table tblMain (to give it a name for
convenience) , linked tables will be properly associated with tblMain. If
VendorNumber changes it won't affect the links, which don't involve
VendorNumber. Neither will changes to Address or other information affect
the links.

If you need to maintain old address information, VendorNumbers, etc. it would
be best to use related tables. Otherwise if you create a new vendor record
in tblMain for a current vendor (because of VendorNumber change, for instance)
it will be necessary to update all of the links. You can use append queries
to create new related tables for VendorNumber, etc. I'm not sure I
understand the situation, so I won't go into a lot of detail, but I have
successfully repaired (normalized) some of my early projects (and buckets of
data provided by others), and may be able to steer you in the right direction
if needed.

Sorry for not being clear. The primary key is an auto number, and the linking
field. Lets say Vendor_ID for the PK (autonumber), and VendorNumber for the
number assigned to the vendor.

Are you saying that Vendor ID rather than the autonumber field is the linking
field?

[quoted text clipped - 9 lines]

 

[Bird Byte]

Thanks Bruce. Vendor_ID is the linking field. As of now, the fields are:
Vendor_ID (primary key auto number)
VendorNumber (number we assign to the vendor, and one vendor may have two
VendorNumbers)
VendorAddress
VendorPhone
VendorCurrent (yes/no field to flag VendorNumber being used)

To achieve normalization, are you suggesting creating a related table that
holds VendorNumber in a table separate from the other (static) vendor
information?

If Vendor_ID is the linking field in the table tblMain (to give it a name for
convenience) , linked tables will be properly associated with tblMain. If
VendorNumber changes it won't affect the links, which don't involve
VendorNumber. Neither will changes to Address or other information affect
the links.

If you need to maintain old address information, VendorNumbers, etc. it would
be best to use related tables. Otherwise if you create a new vendor record
in tblMain for a current vendor (because of VendorNumber change, for instance)
it will be necessary to update all of the links. You can use append queries
to create new related tables for VendorNumber, etc. I'm not sure I
understand the situation, so I won't go into a lot of detail, but I have
successfully repaired (normalized) some of my early projects (and buckets of
data provided by others), and may be able to steer you in the right direction
if needed.

Sorry for not being clear. The primary key is an auto number, and the linking
field. Lets say Vendor_ID for the PK (autonumber), and VendorNumber for the
number assigned to the vendor.

Are you saying that Vendor ID rather than the autonumber field is the linking
field?

[quoted text clipped - 9 lines]

I'm grateful for any ideas.

--



.