This Microsoft Access tutorial explains how to update table records from another table or query.
Access update table records
You can always update table records manually by viewing the table and then enter the value. With Update Query, you can update multiple table records that meet defined criteria, or update table records from another table.
Note that you may update data in an Access table, but you cannot update data in linked table. For example, if your Access table is linked to Excel spreadsheet, you cannot update any data in the spreadsheet from Access.
Update table records that meet criteria
Suppose we have created a table called employee_tbl, now we want to update David’s salary to 11000.
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| 001 | David | HR | 10000 |
| 002 | Mary | Finance | 20000 |
| 003 | Peter | HR | 30000 |
Navigate to Create tab > Query Design > Update
Add the employee_tbl table and enter the criteria as below. Field and Table are one you want to update, Update To is the new value you want to put.
Click on the Run button to run the Query, now David’s salary is updated to 11000.
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| 001 | David | HR | 11000 |
| 002 | Mary | Finance | 20000 |
| 003 | Peter | HR | 30000 |
Note that “Run” button executes the Query and updates the Table, but “View” button only preview the field values after update.
You may also directly type SQL in SQL View
UPDATE employee_tbl SET Salary = 11000 WHERE [Employee ID]="001"
You can update more than one fields. For example, to also update the Department of David to Customer Service
UPDATE employee_tbl SET Salary = 11000, Department = "Customer Service" WHERE [Employee ID]="001"
If you remove the WHERE statement, all records will be updated.
Update multiple records from another table
To update multiple records, first prepare a Table (or Query) that contains new salary of employees. I personally prefer to prepare such Table in Excel and then use Linked Table because it is more flexible to maintain the table in Excel.
| Employee ID | Salary |
|---|---|
| 001 | 11000 |
| 002 | 22000 |
| 003 | 33000 |
Create an Update Query, join the two tables as below by Employee ID. In Update To, specify the Table name of the Salary field.
Run the Query, the Salary are updated.
| Employee ID | Employee Name | Department | Salary |
|---|---|---|---|
| 001 | David | HR | 11000 |
| 002 | Mary | Finance | 22000 |
| 003 | Peter | HR | 33000 |
In SQL View
UPDATE employee_tbl INNER JOIN new_salary_tbl ON employee_tbl.[Employee ID] = new_salary_tbl.[Employee ID] SET employee_tbl.Salary = [new_salary_tbl].[Salary];