INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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

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

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

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

(OP)

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

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

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.

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

(OP)

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];

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

So you have this SQL:

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]; 
 
And you need something like this (un-tested)

UPDATE tblMaster 
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]; 
 


Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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

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

(OP)
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];

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

(OP)
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.

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

This Update of yours will not work: sad

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]; 
 
because you are trying to Update tblMaster, which is OK, but you say:
SET Fixed_Address4.Address to be equal to [tblMaster].[ADDRESS]
etc. which is wrong.
it needs to be the other way around:
SET [tblMaster].[ADDRESS] = Fixed_Address4.Address,
etc.

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

(OP)
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];

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

Can you do this?

Select tblMaster.ID, FixedAddress.ID
From tblMaster, FixedAddress
Where tblMaster.ID = FixedAddress.ID


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.

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

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

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

(OP)
Anyone?

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

(OP)
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.

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

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

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

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.

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

(OP)
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 Accessdazed

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close