Learn How to Set Up an Update Query in Microsoft Access

Last updated on October 8th, 2023 at 02:45 pm

If you are here, chances are you have been working with or studying Access databases and you are interested in learning about the MS Access update query.

Queries are very powerful. They help us to search, manipulate and change information in thousands of records quickly and easily. You will never be an expert at databases unless you can master queries. So what is an update query exactly?

If you wish to learn about how to set up reports and forms, visit Access report and form.

What is an update query?

An update query is a powerful tool in a database. It allows a user to make changes to thousands of records in a database in seconds.

As the name suggests, the update query is used to change the information (update) based on some criteria and calculations. This is not to be confused with the one where we create calculated fields in Access queries.

Are you new to databases and are struggling to understand all the new terms? Then be sure to read introduction to databases.

When do we use an update query – Access query examples

We use an update query when we need to change the information that has already been entered into the database.

Example 1

Let’s imagine that the person working with the database incorrectly spelled the word Pomeroon in the students’ PersonalData table. You need to change (update) the information to reflect the correct spelling.

Examle 2

For instance, you have a student database for all the students of Charity Secondary School. The database contains the CSEC payments that each student must make in order to be able to write their examination. However, the person who entered the information used last year’s payment structure. This year’s CSEC payment is 10% higher than last year’s. So, the person working with this database needs to update the payment information to reflect this change.

Instead of going to the table and changing the information for all the students individually, we can use an update query to change all the students’ information in less than 5 minutes, regardless of whether there are 10 or 10,000 students in the database.

If you are interested in learning about other types of queries, be sure to read our articles on creating select queries in Access and how to create and work with queries with criteria.

How to create an update query – Example 1

Now let us see how we will create an update query with the first scenario above. For both query examples, we will use Office 2016, and for the first query, we will use the information in the table below.

Now follow these steps:

1. Go to the Create toolbar.
2. Choose Query Design.
3. Next, select the table from those in the list on your right. To do this, you can simply double-click on it.
4. Next, add the field(s) that you wish to change by clicking the area-marked field at the bottom of your screen. In the drop-down list, choose the relevant field(s).
5. Go to the query design tab. choose Update. You should now be able to see the Update To option;
6. Now under Village, where the Update To option is, type “Pomeroon”.
7. Under criteria, put the name of the village that you wish to change to Pomeroon. In our case, we are replacing the word omeroon with Pomeroon. So for criteria type “omeroon”.
8. Finally, go to the Query Design tab and click run.

Unlike some other queries, you will not be able to see the result of this query in the Datasheet View. The change will be visible in the Personal Data table. See the result of the change in the picture below.

If you are new to databases and would like to learn more, visit how to create tables and set data types.

Example 2

For our next query, we will use the CSEC Payment table below:

So to create the second update query, follow steps 1 to 4 in the above example. Once you have completed that continue with the steps below:

1. For this query, the field we wish to update is named Payments.
2. On the Query Design tab, choose update.
3. You should now see the Update To option at the bottom of the screen.
4. In the Update To field, under Payments, type: [Payments]*1.1.
5. Next, on the Query Design tab, click Run.

Open the CSEC Payments table to see the updated table.

Primary keys are very important when setting up databases. Read database keys explained.

Before you go

We try our best to be as detailed and clear as possible in the information we provide. However, if you have any questions or comments, be sure to leave them in the comments section below.