A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Multiple id problem



 
 
Thread Tools Display Modes
  #1  
Old April 5th, 2010, 04:16 PM posted to microsoft.public.access.tablesdbdesign
Bird Byte
external usenet poster
 
Posts: 15
Default Multiple id problem

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.
Ads
  #2  
Old April 5th, 2010, 04:36 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Multiple id problem

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.
  #3  
Old April 5th, 2010, 04:54 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Multiple id problem

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.



  #4  
Old April 5th, 2010, 09:46 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Multiple id problem

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 ........;

--
Build a little, test a little.


"Jeff Boyce" wrote:

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.



.

  #5  
Old April 8th, 2010, 01:00 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Multiple id problem

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

  #6  
Old April 12th, 2010, 04:44 PM posted to microsoft.public.access.tablesdbdesign
Bird Byte
external usenet poster
 
Posts: 15
Default Multiple id problem

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

.

  #7  
Old April 12th, 2010, 08:03 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Multiple id problem

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

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


--
Message posted via http://www.accessmonster.com

  #8  
Old April 12th, 2010, 10:39 PM posted to microsoft.public.access.tablesdbdesign
Bird Byte
external usenet poster
 
Posts: 15
Default Multiple id problem

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.

"BruceM via AccessMonster.com" wrote:

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

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


--
Message posted via http://www.accessmonster.com

.

  #9  
Old April 13th, 2010, 12:34 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Multiple id problem

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.


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


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

  #10  
Old April 14th, 2010, 05:32 PM posted to microsoft.public.access.tablesdbdesign
Bird Byte
external usenet poster
 
Posts: 15
Default Multiple id problem

Thanks Bruce. Vendor_ID is the linking field. As of now, the fields a
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?


"BruceM via AccessMonster.com" wrote:

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.


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


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

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.