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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|