Multi-Table Update Query in MS Access

Multi-Table Update Query in MS Access
In this episode, we’ll look at how to update one table with values from another table in Microsoft Access. We’ll show a basic single-table update first, on a single column, then move to a more complex example using two tables, with one table being updated from the other. Update queries are one of the main types of queries in MS Access, so if you learn this skill, you’ll have one more great tool in your toolbox!
Related Videos:
How to Make an Inner Join and Left Join in Microsoft Access and Learn How to Compare Lists
• How to Make an Inner J...
How to Make a Query by Joining Tables and Making a Calculated Column in Microsoft Access
• How to Make a Query by...
How to Use an Append Query in MS Access: mdb to accdb
• How to Use an Append Q...
Multi-Table Update Query in MS Access
You are watching this video now!
How Use Delete Queries in MS Access'
• How Use Delete Queries...
How to use In.. Not In.. to include or exclude multiple values in Microsoft Access
• How to use In.. Not In...
How to Use Wildcards in MS Access Queries
• How to Use Wildcards i...
How to Use Double and Single Quotes in MS Access Queries
• How to Use Double and ...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziema...
access update query, access update one table from another, ms access sql, access single table update, access multi table update, Sean Mackenzie Data Analytics and Data Engineering
#MultiTableQueryMSAccess #UpdateQueryMSAccess
• Multi-Table Update Que...

[@siphomaleka4681]

Exactly what I was looking for.

[@seanmackenziedataengineering]

Awesome! Glad to hear

[@BinarKing1]

so usefulllllll,thank u.keep going

[@seanmackenziedataengineering]

Thank you, I will!

[@tungduongthe7826]

I want to change 1 character in the table table . For example, like "T001" to "CH001", how should the query part be done? Thank you!

[@seanmackenziedataengineering]

Good question! Update MyTable Set MyField = Replace(MyField, "T", "CH"); Or, create new query, select Update query on Ribbon, put MyTable in query, double click on field to add it to grid, in "Update to:" put in Replace(MyField, "T", "CH") Good luck!

[@seanmackenziedataengineering]

Just beware, that will update every "T" in the table, in every record! If you just want rows starting with "T", then add a where clause, ie. Where Left(MyField, 1) = "T"

[@timpierson8371]

Is there a way to download your sample data to follow along with you?

[@seanmackenziedataengineering]

Some episodes have the data available at mackenziemackenzie.com/downloads - you can check by date for the episode you're watching. I don't think I have this video's data but am trying to put more up to work with!

[@manishateegala6331]

I have 2 tables ..by updating table1 table 2 get updated is it possible

[@seanmackenziedataengineering]

You bet. You can use a data macro for this: kzbin.info/www/bejne/jXq6o4CsoNhkjdE Also watch the DM intro: kzbin.info/www/bejne/rabceqCroceSq5Y An alternate way is to use a query after a form is updated: kzbin.info/www/bejne/qaW0maV8jpmebqc

[@vishnusankarankottarappatt7882]

Dear Sir, Thank you for the prompt response. After sending the update to you, I was still working on this to find out the issue and I managed to crack down the problem. Though the table was showing indexed when you verify, actually the actual index was on a different field. I offloaded the table to an excel file and reimported and found this. After changing the index from excel file, the program started working fine. But the problem is that I cannot change the index. So currently I am taking a longer way which is taking the input file from a query. Please let me know if you have better ways.

[@seanmackenziedataengineering]

Nice work, you found the problem. You could create a new table with the first field as MyID with type Autonumber. Then, all of the fields of your source table. Append your table into this new table (using Append query). Use the new table for your update as it does not have the old indexes that caused the failure. You can use the procedure method which is much faster.