Adding AutoNumber as a PrimaryID to an Existing Table

[freddawson]

I apologize for maybe not posting this question in the correct forum, but I wasn't quite sure how to categorize it. Also, I am an Access novice, so I'll try to explain my wants/needs as best as possible.



I have a simple database that I inherited, that contains basic customer contact info. We want to start adding more functionality to the database, so I've done alot of research to learn about how to begin normalizing my data.

The existing table does not contain a useful primary key, since we may have multiple individuals from the same company, companies with very similar names, etc.

I would like to add an AutoNumbered field to use as a CustomerID/primary key, but I can't seem to find a way to do so. Here is what I've tried so far:

• I have tried to add a field to the table, and make it an AutoNumber data type. When I do so, I can error message saying that I cannot make a field AutoNumber if any other field in the table already has data entered in it.
• I have tried to create a new table containing only an AutoNumber PrimaryID field, and then import data from the existing table, but that just creates a 3rd table.
• Lastly, I did some research here on the forum, and it seems to me I can accomplish this by usign queries, but I must admit I don't understand the process enough to pull it off.

This seems like it should be a pretty simple process (for those who know what they're doing!), but I just feels like I'm missing something.

Thanks in advance for any feedback.

[jzwp11]

Before you go through the trouble of adding a primary key, it would be better to get the new table structure set up first and then move your data into the new structure.

You said that you have companies and people/contacts at those companies. That would require at least 2 tables

tblCompany
-pkCompanyID primary key, autonumber
-txtCompanyName

tblCompanyContacts
-pkCompanyContactID primary key, autonumber
-fkCompanyID foreign key to tblCompany
-txtFName
-txtLName

The above is just a simple structure, what you ultimately need will depend on what other data you have or intend to have in the future. Could you please provide more details on that?