Assigning ID by group

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

"John" wrote in message
...
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.

--
Brendan Reynolds