Access Update Table Records

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.

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

 

Leave a Reply

Your email address will not be published.