Update records based on an identifying feild from another table(xpost)

[scotent]

I have a list of companies that have a currently empty field for 'Business Sector' I have another table thta contains a portion of the records that have this feild filled in. What is the best procedure for running a query that will update the fields in the new table based on the existing data in the old table?

The company names are the identifier that I can use, I am just a little puzzled as to where to start with the SQL.

Thanks in advance

 

[scotent]

I have been messing around with this code

UPDATE CompanyContacts
SET CompanyContacts.BusinessSector = CompanyContactsold.BusinessSector
WHERE CompanyContacts.CompanyName = CompanyContactsold.CompanyName

Not working tho.

 

[WildHare]

Bring both tables into the query view.
Join the two based on the Company Name.
Drag the BusinessSector field from your "new" table into the grid.
Change the query to an UPDATE query.
Set UPDATE TO clause to

[CompanyContactold]![BusinessSector]

Run the query.



Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[scotent]

FInal version

UPDATE tblCompanies INNER JOIN test ON tblCompanies.CompanyName = test.CompanyName SET test.BusinessSector = tblCompanies.[Business Sector];

Thanksfor the help guys