Create and run an update query

In this article

• Overview

• Using an update query

• Update data from one table to another

• Stop Disabled Mode from blocking a query

• SQL version: UPDATE statement

Overview

Here are the similarities and differences between Find and Replace and an update query:

Like the Find and Replace dialog box, an update query lets you specify which value is being replaced, and what the new value is.

Unlike the Find and Replace dialog box, an update query lets you:

• Use criteria that do not depend on the value that you want to replace.

• Update lots of records in one pass.

• Change records in more than one table at the same time.

Using an update query

As a best practice when creating an update query, first create a select query that identifies the records that you want to update, and then convert that query to an update query that you can run to update the records. By selecting data first, you can verify that you’re updating the records you want before you actually change any data.

How to back up your database

1. Click the File Tab, and then click Save As. 

2. On the right, under Advanced, click Back Up Database.

3. In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save.

   Access closes the original file, creates a backup, and then reopens the original file.

   To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and then open the renamed backup copy in Access.

In this section

• Step 1: Create a select query to identify the records to update

• Step 2: Update the records

Step 1: Create a select query to identify the records to update

1. Open the database that contains the records you want to update.

2. On the Create tab, in the Queries group, click Query Design. 

3. Click the Tables tab.

4. Select the table or tables that contain the records that you want to update, click Add, and then click Close.

   The table or tables appear as one or more windows in the query designer, and the windows list all the fields in each table. This figure shows the query designer with a typical table.

   

   1. Table shown in the query designer

   2. Query design grid

5. Double-click the fields that you want to update in the table windows. The selected fields appear in the Field row in the query design grid.

   You can add one table field per column in the query design grid.

   To add all the fields in a table quickly, double-click the asterisk (*) at the top of the list of table fields in the table window. This figure shows the query design grid with all the fields added.

   

6. To limit the query results based on field values, in the query design grid, in the Criteria row, enter the criteria that you want to use to limit the results.

   Table of example criteria

   The following table shows some example criteria and explains the effect that they have on the results of a query.

   : Many of the examples in this table use wildcard characters to make the query more flexible or powerful.

   Criteria	Effect
   >234	Returns all numbers greater than 234. To find all numbers less than 234, use < 234.
   >="Cajhen"	Returns all records from Cajhen through the end of the alphabet.
   Between #2/2/2020# And #12/1/2020#	Returns dates from 2-Feb-17 through 1-Dec-17 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotation marks (') instead of pound signs (#). Example: Between '2/2/2020' And '12/1/2020' .
   Not "Germany"	Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".
   Not "T*"	Finds all records except those starting with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
   Not "*t"	Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
   In(Canada,UK)	In a list, finds all records containing Canada or UK.
   Like "[A-D]*"	In a text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
   Like "*ar*"	Finds all records that include the letter sequence "ar". If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
   Like "Maison Dewe?"	Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark (?).
   #2/2/2020#	Finds all records for February 2, 2020. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks (') instead of pound signs (#); for example, ('2/2/2020').
   < Date() - 30	Uses the Date function to return all dates more than 30 days old.
   Date()	Uses the Date function to return all records containing today's date.
   Between Date() And DateAdd("M", 3, Date())	Uses the Date and DateAdd functions to return all records between today's date and three months from today's date.
   Is Null	Returns all records that contain a null (blank or undefined) value.
   Is Not Null	Returns all records that contain a value.
   ""	Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, you enter a pair of double quotation marks with no space between them ("") instead of a number.

7. On the Query Design tab, in the Results group, click Run.

8. Verify that the query returns the records that you want to update.

9. To remove any fields that you do not want included in the query design, select the fields and then press DELETE.

10. To add any fields that you want to include in the query design, drag the additional fields to the query design grid.

Step 2: Update the records

1. On the Query Design tab, in the Query Type group, click Update.

   This procedure shows you how to change a select query to an update query. When you do this, Access adds the Update to row in the query design grid. The following illustration shows an update query that returns all the assets purchased after January 5, 2005 and changes the location to "Warehouse 3" for all the records that meet that criterion.

   

2. Locate the field that contains the data that you want to change, and then type your expression (your change criteria) in the Update to row for that field.

   You can use any valid expression in the Update to row.

   Table of example expressions

   This table shows some example expressions and explains how they change data.

   Expression	Result
   "Salesperson"	In a Short Text field, changes a text value to Salesperson.
   #8/10/20#	In a Date/Time field, changes a date value to 10-Aug-20.
   Yes	In a Yes/No field, changes a No value to Yes.
   "PN" & [PartNumber]	Adds "PN" to the beginning of each specified part number.
   [UnitPrice] * [Quantity]	Multiplies the values in fields named UnitPrice and Quantity.
   [Freight] * 1.5	Increases the values in a field named Freight by 50 percent.
   DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])	Where the ProductID values in the current table match the ProductID values in table named Order Details, this expression updates sales totals by multiplying the values in a field named Quantity by the values in a field named UnitPrice. The expression uses the DSum function because it can operate against more than one table and table field.
   Right([ShipPostalCode], 5)	Truncates (removes) the leftmost characters in a text or numeric string and leaves the 5 rightmost characters.
   IIf(IsNull([UnitPrice]), 0, [UnitPrice])	Changes a null (unknown or undefined) value to a zero (0) value in a field named UnitPrice.

3. On the Query Design tab, in the Results group, click Run.

   An alert message appears.

4. To run the query and update the data, click Yes.

   : When you run the query, you might notice that some fields are missing from your result set. If your query contains fields that you don't update, Access does not display those fields in the results, by default. For example, you might include ID fields from two tables to help ensure that your query identifies and updates the correct records. If you don't update those ID fields, Access does not display them in the results.

Top of Page

Update data from one table to another

When you need to update data from one table to another, consider the following rule: the data types for the source and destination fields must either match or be compatible.

Furthermore, when you update data from one table to another and use compatible data types instead of matching data types, Access converts the data types of those fields in the destination table. As a result, some of the data in the destination fields may be truncated (deleted). The section Restrictions on data type conversions lists the ways in which you can and cannot convert data types. The table in this section also explains when converting a data type can change or eliminate some or all the data in a field, and what data might be eliminated.

The process of updating data from one table to another follows these broad steps:

1. Create an update query and add both the source and destination tables to the query.

2. Join those tables on the fields that contain the related information.

3. Add the names of your destination fields to the Field row of the query design grid.

4. Add the names of your source fields to the Update To row of the query design grid by using the following syntax: [source_table].[source_field].

The steps in this section assume the use of two similar tables. In this example, the Clients table is located in a database that you just inherited, and it contains more recent data than the Customers table. You can see that some of the manager names and addresses have changed. For that reason, you decide to update the Customers table with the data from the Clients table.

Create and run the update query

1. On the Create tab, in the Queries group, click Query Design. 

2. Select Add Tables.

3. Double-click your source and destination tables to add them to the query. Each table appears in a window in the query designer.

4. In most cases, Access automatically joins related fields in a query. To manually join fields that contain related information, drag the related field from one table to the equivalent field in the other table.

   For example, if you use the sample tables shown earlier, you drag the Client ID field to the Customer ID field. Access creates a relationship between those fields in the two tables and uses that relationship to join any related records.

5. On the Query Design tab, in the Query Type group, click Update.

6. In the destination table, double-click the fields that you want to update. Each field appears in the Field row in the query design grid.

   If you use the sample tables, you add all fields except the Customer ID field. Notice that the name of the destination table appears in the Table row of the design grid.

7. In the Update To row of the query, in each of the columns that contains a destination field, add the name of the source table and the field in the source table that corresponds to the field in the destination table, and make sure that you use this syntax: [Table].[Field], where you enclose table and field names with square brackets, and you separate table and field names with a period.

   This figure shows part of the design grid, using the sample tables. Note the syntax for the table and field names in the Update To row.

   

   As you continue, remember that you must spell the table and field names in the Update To row correctly, and you must match any punctuation in the original table and field names. However, you do not have to match capitalization.

8. On the Query Design tab, in the Results group, click Run.

9. When asked to confirm the update, click Yes.

Restrictions on data type conversions

The following table lists the data types that Access provides, explains any restrictions on how to convert data types, and briefly describes any data loss that might occur during conversion.

Top of Page

Stop Disabled Mode from blocking a query

If you try to run an action query and it seems like nothing occurs, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

By default, Access disables all action queries (update, append, delete, or make table queries) unless your database is in a trusted location or the database is signed and trusted. If you have not taken either of these actions, you can still enable the query for the current database session by clicking Enable Content on the Message Bar.

Top of Page

SQL version: UPDATE statement

If you're comfortable working with SQL, you can also write an UPDATE statement by using SQL view. To use SQL view, create a blank, new query, and then switch the SQL view.

This section presents the syntax for and an example of an UPDATE statement.