Скачать или смотреть How to Update a Table Using Data from Another Table in Microsoft Access

Learn how to effectively `update your Microsoft Access tables` with data from another table using SQL queries. Follow our guide for a step-by-step solution!
---
This video is based on the question https://stackoverflow.com/q/64669907/ asked by the user 'jye0325' ( https://stackoverflow.com/u/9609663/ ) and on the answer https://stackoverflow.com/a/64671530/ provided by the user 'Andre' ( https://stackoverflow.com/u/3820271/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How do I Update Table with a Query from another Table Microsoft Access

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Update a Table Using Data from Another Table in Microsoft Access

Managing databases efficiently is critical for all users of Microsoft Access, whether you're designing a small personal database or managing a larger business application. One common challenge is updating records in one table based on the data from another table. In this guide, we will tackle this issue by providing a clear method of using SQL queries to perform such updates seamlessly.

The Problem: Updating Table A with Quantities from Table B

Imagine you have two tables in your Access database. Table A contains item details, and Table B contains reported quantities for those items. You want to update the "Quantity to Date" column in Table A with the corresponding "Reported Quantity" from Table B. Below is a quick look at the data:

Table A

Item NumberIDDescriptionUnit PriceQuantity to Date5b$12.001a$12.002b$3.003a$5.00Table B

Item NumberIDReported Quantity1a102b203a109c232Your goal is to update Table A so that it reflects the quantities recorded in Table B, resulting in an updated "Quantity to Date" column:

For example, Item Number 1 and ID a should have a Quantity to Date of 10 from Table B.

The Solution: SQL Update Query with INNER JOIN

To achieve this, you can utilize a straightforward SQL UPDATE query that uses an INNER JOIN between the two tables. Here’s how to construct the SQL code to accomplish this task:

Step-by-Step SQL Query

Write the UPDATE Statement: Start by specifying which table to update.

Join the Tables: Utilize INNER JOIN to link records from both tables based on the common fields (Item Number and ID).

Set the Values: Indicate the new value for the Quantity to Date column.

Here is what the SQL query would look like:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query Components

UPDATE Statement: This tells Access which table you want to modify.

INNER JOIN: This part of the query allows you to match records from Table A and Table B based on the specified conditions (Item Number and ID).

SET Clause: This assigns the value from Table B (Reported Quantity) to the relevant column (Quantity to Date) in Table A.

Final Thoughts

Once you run this SQL query in your Microsoft Access query editor, Table A will successfully update with the quantities from Table B!

Example Result

After executing the query, Table A will look like this:

Item NumberIDDescriptionUnit PriceQuantity to Date5b$12.001a$12.00102b$3.00203a$5.0010By following these steps, you can manage and update your tables effectively in Microsoft Access for a smoother workflow. Happy databasing!