Update Table Address City and Zip from New Table with corrected Info.

[Dom606]

I have a master table (tblMaster) with ID, Address, City, and Zip and many other fields. I also have a spreadsheet with corrected information I imported into a table named "Fixed Address4". The imported table contains ID, Address, City and Zip. I can join the two tables via the ID which is the primary key in both tables.

Now, my question is how do I create an update query that will change the data in tblMaster with that contained in Fixed Address4 where the ID's are equal? There are several hundred rows of data that need to be processed.

I looked at the update query in Access but it will update a field to something a user supplies. This will not work. So I must need some form of SQL, I think. This is Access 2016. Any help will be appreciated.
Thanks
Dom

 

[MajP]

You join your two tables. Then you select update icon (explanation point and pencil). You pick all the fields you want to update. In the row "update" you type the [tableName].[FieldName] from which to update. The intellisense will do all the work once you start typing. In the below example I am updating TbStates.StateName with TblUpdate.FixName

 

[Dom606]

I did what you suggested but it seems to be selecting the old data not the new from Fixed Address4.

 

[Andrzejek]

Somewhere in this environment you should have an SQL with your actual Update statement. Could you share it here?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Dom606]

Thank you for looking at this.

UPDATE [Fixed Address4] INNER JOIN tblMaster ON [Fixed Address4].ID = tblMaster.ID SET tblMaster.ADDRESS = [Fixed Address4].[Address], tblMaster.CITY = [Fixed Address4].[City], tblMaster.ZIP = [Fixed Address4].[ZIP];

 

[Andrzejek]

So you have this SQL:

[pre]
UPDATE [red][Fixed Address4] [/red]
INNER JOIN tblMaster ON [Fixed Address4].ID = tblMaster.ID
SET tblMaster.ADDRESS = [Fixed Address4].[Address],
tblMaster.CITY = [Fixed Address4].[City],
tblMaster.ZIP = [Fixed Address4].[ZIP];
[/pre]
And you need something like this (un-tested)

[pre]
UPDATE [blue]tblMaster [/blue]
INNER JOIN tblMaster ON
tblMaster.ID = [Fixed Address4].ID
SET tblMaster.ADDRESS = [Fixed Address4].[Address],
tblMaster.CITY = [Fixed Address4].[City],
tblMaster.ZIP = [Fixed Address4].[ZIP];
[/pre]




Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Dom606]

I get an error on the join. On [Fixed Address4].ID

 

[Dom606]

Andy, thanks for your help. What I did is as follows and it seems to be working as planned.

UPDATE tblMaster INNER JOIN Fixed_Address4 ON tblMaster.ID = Fixed_Address4.ID SET Fixed_Address4.Address = [tblMaster].[ADDRESS], Fixed_Address4.City = [tblMaster].[CITY], Fixed_Address4.ZIP = [tblMaster].[ZIP];

 

[Dom606]

Andy, I spoke too soon. That did the opposite of what I needed done. I don't understand how it updates in one direction and not the other. Totally confused.

 

[Andrzejek]

This Update of yours will not work:

[pre]
UPDATE tblMaster
INNER JOIN Fixed_Address4
ON tblMaster.ID = Fixed_Address4.ID
SET Fixed_Address4.Address = [tblMaster].[ADDRESS],
Fixed_Address4.City = [tblMaster].[CITY],
Fixed_Address4.ZIP = [tblMaster].[ZIP];
[/pre]
because you are trying to [tt]Update tblMaster[/tt], which is OK, but you say:[tt]
SET Fixed_Address4.Address to be equal to [tblMaster].[ADDRESS][/tt] etc. which is wrong.
it needs to be the other way around:[tt]
SET [tblMaster].[ADDRESS] = Fixed_Address4.Address, [/tt]etc.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Dom606]

Ok. I am now simply trying to do an update on one field. And it fails on the Join syntax error and it points to the BOLD field in the SQL. I have deleted the table and recreated it without a space in the name. It still does not work. I even put in a where tblMaster.ID = FixedAddress.ID and that did not help.


UPDATE tblMaster
INNER JOIN tblMaster ON FixedAddress.ID = tblMaster.ID
SET tblMaster.ADDRESS = [FixedAddress].[Address];

 

[Andrzejek]

Can you do this?
[tt]
Select tblMaster.ID, FixedAddress.ID
From tblMaster, FixedAddress
Where tblMaster.ID = FixedAddress.ID
[/tt]

Do you get any outcome out of this SQL? Or does it error?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Dom606]

That did not error. It gave me results or each row.

 

[Dom606]

Anyone?

 

[Dom606]

OK. Solved. This is what worked.

UPDATE tblMaster INNER JOIN FixedAddress ON tblMaster.ID = FixedAddress.ID SET tblMaster.ADDRESS = [FixedAddress].[Address], tblMaster.CITY = [FixedAddress].[City], tblMaster.ZIP = [FixedAddress].[ZIP];

When you run an update query and view the data before actually hitting RUN it shows a data table but not the data that will be updated. What a joke. I simply hit RUN, was told 800 records were updated. I then checked the tblMaster and all the updates took place. Go figure.

Andy thanks for your help on this one. It took me all day but finally got it to work.

 

[Andrzejek]

Congrats!
So you did exactly what I suggested in my post on 8 Dec 16 18:35

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[sxschech]

With Action queries such as update and delete the view button will show you the records to be affected by the change rather than what the change will look like. It is useful for that purpose so that if you view the data and were expecting to update 500 rows and instead you get nothing or the entire data set, you know something is wrong with the query, which is better than finding out after you hit the run button and all the data are changed/gone. If you haven't experienced how the view works before it could be disconcerting to see data and not have the changes you expected show up.

 

[Dom606]

sxschech You are so correct. I was looking for the changed data to show and when it did not I figured (wrong) that it did not work. Learn something new every day. I still love Access