Update Query in Access 2010-Update based on matching fields

0
votes

I'm an intern at a small company who is making some fixes to a 15 year old access database that is used to keep track of customers, jobs, etc. I'm trying to write an update query and can't seem to get to work the way I need it to. Here is my sitation.

I have two tables. One contains Company information. The other contains Contact information. The primary key of the company table is also in the contact table, for a one-to-many relationship. E.g., if a company has 3 contacts, the CompanyID number that is from the primary key of the Company table is associated with the three contacts in the Contacts Table.

Ok. Now the problem is that there is some legacy code in a form, called "Company", that searches simultaneously for both contacts and companies. The problem is that there is a field in the contacts table called "ContactCompany" that is being used by this old search function. If a user changes the Company name with the form, the Company name is updated on the Company table, but NOT in the Contacts table.

I decided that an update query would be the way to go, to sync up the Company Name information on both forms. I tried to design it so that if the ID numbers matched between the tables, the company name in the Company table would get copied over to the contacts table. My code is below:

UPDATE Company INNER JOIN Contacts ON (Company.ID = Contacts.CompanyID) AND (Company.ContactCompany = Contacts.ContactCompany) SET Contacts.ContactCompany = [Company].[ContactCompany] WHERE (([Contacts].[CompanyID]=[Company].[ID]));

When I run the query Access tells me that it is updating X number of records, but the records do not update.

Please note that I used query builder; the SQL code was auto-generated by access when I go into SQL view.

I'm not very experienced with VB; all of my knowledge has been from googling stuff. If someone could give me some pointers on what I'm doing wrong or how to proceed I'd be grateful. Thanks!

2
sqldatabasems-accessvbams-access-2010
Hello Someguy welcome to StackOverflow, next time try to provide a SqlFiddle so we can understand the problem better and give you an answer much faster – Also please read How to ask and How to create a Minimal, Complete, and Verifiable example. – Juan Carlos Oropeza
Thank you, I didn't know about SqlFiddle. – someguy

2 Answers

0
votes

Because in your join condition you are selecting the Contact.ContactCompany which already has same ContactCompany name.

AND (Company.ContactCompany = Contacts.ContactCompany)

Remove that "AND" part and your query should look something like this.

UPDATE Company 
INNER JOIN Contact ON Company.ID = Contact.CompanyID 
SET Contact.ContactCompany = [Company]![ContactCompany] 
WHERE (([Contact]![CompanyID]=[Company]![ID]));

P.S. Make sure you have backup of your database before updating

0
votes

I use the query assitance from Access

You dont need AND (Company.ContactCompany = Contacts.ContactCompany) you are already saying those are different now.

UPDATE Company 
INNER JOIN Contact 
      ON Company.CompanyID = Contact.CompanyID 
SET Contact.CompanyName = [Company].[CompanyName];

You could add this line to only update those different 

WHERE Contact.CompanyName <> = [Company].[CompanyName]

enter image description here