View Single Post
  #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