Updating Records in Microsoft Access with an Update Query:
A situation may arise where we need to update many records in a database
table when certain information changes or needs to be modified.
A Microsoft Access Update Query updates specified values in a table
for all records or for those records that match a specified criteria.
It is possible to update records manually in a database table, either
via a form or through the tables datasheet, however this may take
a very long time. Changing records manually is not only time consuming,
it is also inefficient, and lends itself to errors as you update the
The best way to handle this type of event, the updating of many records
in a database table, is to use an Update Query to make the changes
to data in one operation. You will save yourself time, and eliminate
the possibility of manual errors.
Note: Due to the permanent effects of working with
an update action query, you should always make a backup copy of your
tables, or your database before attempting this option.
How to create a Microsoft Access Update Query:
- Create a SELECT query to determine the records that will be updated.
Apply any required query criteria, and view the data that will be
updated by pressing the Datasheet button.
- In the query design view, click on the drop-down arrow to the
right of the Query Type button and choose Update Query.
- After you are satisfied that the information to be updated is
correct Run the query using the Run
Now consider the following scenario example, that will illustrate
the use of a Microsoft Access Update query:
One of your product suppliers has decided to increase the price of
their products by 3 percent (%) across the board. You have decided
that you will use an Update Query to change all of the relevant records
in your Products table.
Our original table appears like the following example:
The original Products table before we run the Update Query
We need to update the Unit Price field, to update all records from
our supplier (Exotic Liquids) as they have informed us of a 3% price
Creating the Update Query
Here are the steps that we follow to produce the required outcome:
- Create a new query using the Products table and the Suppliers
table. Include the fields that you are going to use to update the
data (ProductID, ProductName and UnitPrice from the Products table,
and CompanyName from the Suppliers table)
We have also included criteria in the CompanyName field to limit
the results to only those of the Supplier that we are updating the
Also, in the image below, you will see that we have included an
additional field, just to test our expression. This will give us
a value for the 3% increase, just to check that the results will
be returned correctly. This field will be removed before we run
the update, but we will use the expression later.
Creating a SELECT query, that will later be changed to the UPDATE
- To check what results this will produce, run the query by clicking
on the datasheet
Checking the results before running the Update Query
- When we are happy with the resulting data, we can switch back
to design view to convert the query to an Update query.
From the Query Type button
on the toolbar, select Update Query
In the curUnitPrice column, in the Update To cell, type in the expression
[curUnitPrice]*1.03 and press enter. This expression
will update the original Unit Price by 3 Percent.
The query design should now look like:
The Update Query design
- We now need to Run this query, using the Run button
to update the data in our Products table that meets the criteria
that is applied. The warning dialog box indicates the number of
records that will be update, click Yes to accept this:
Remember that the update query will permanently update records from
the specified table(s), therefore it is very important that you have
backed up the table(s) or database before running this object.
Check Your Update Query Results
Once you have ran the update query, you can check the results by
once again changing the update query back to a select query.