Assigning ID by group

[John]

Hi

I have a table of contacts with their associated companies. How can I assign
numeric company ids in sequence to these records such any records with the
same company names get the same company ids?

Thanks

Regards

 

[Brendan Reynolds]

Hi

I have a table of contacts with their associated companies. How can I
assign numeric company ids in sequence to these records such any records
with the same company names get the same company ids?

Thanks

Regards

Assuming the following tables and columns ....

Companies (CompanyID, CompanyName)
Contacts (ContactID, CompanyName, etc.)

First add a CompanyID field to the Contacts table, then run an update query
like the following ....

UPDATE Contacts INNER JOIN Companies ON Contacts.CompanyName =
Companies.CompanyName SET Contacts.CompanyID = [Companies].[CompanyID];

BTW, it would be a mistake to have both CompanyID and CompanyName in the
Contacts table. I'm assuming that you're planning on removing the
CompanyName column from the Contacts table after the CompanyID column has
been added and updated.