Assigning ID by group

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
John said:
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
Click to expand...


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.
 
Back
Top