How do I change primary key

[glenn69]

Hello,

I have a database in Access 2000 that was written somewhat hastily. I would like to make some specific repairs.

There are 2 linked tables.
1) tblContacts (containing contact info including street address without city, state, and zipcode)
2) tblTowns (containing city, state, zipcode)

The relationship one (tblContacts) to many (tblTowns). Primary key in tblTowns is the name of the city.

Problems that arise: When a new contact is from a city that has the same name as a city in another state. I will get duplicate reports since city X is now a duplicated primary key.

I would like to create a new primary key maybe an autonumber generated key in tblTowns. Is there a standard methodology of handling this type of a change. If I just add the ID column to tblTowns, then I need to populate the foreign key field in tblContacs...how is that best done?

Hope the question is not to open ended. I'd appreciate any help that can be offered.

 

[redneckgeek]

Since city is your primary key, you must not have duplicate info... yet.

Add your autonumber key (CityID) to tblTowns, and make that the primary key. Add CityID as a foreign key to tblContacts. Run an update query on tblContacts, updating CityID with info from tblTowns. You can still link on city in your query, since there's no duplicates yet. Then delete the city column from tblContacts.