Create and run an update query

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

You deploy update queries in Access databases to append, change, or delete the information in an existing record. You can envision update queries as a powerful form of the Find and Replace dialogue box. You cannot apply an update query to add new records to a database, or to erase records from a database.

To create new records to a database you use an append query, and to delete whole records from a database you use a delete 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 dialogue box, an update query lets you specify which value is being replaced, and what the new value is.

Unlike the Find and Replace dialogue 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.

Restrictions on fields that can be updated

An update query cannot be used to update data in the following types of fields:

  • Calculated fields –   The values in calculated fields do not permanently reside in tables. They only exist in your computer’s temporary memory after Access calculates them. Because calculated fields do not have a permanent storage location, you cannot update them.
  • Fields from a totals query or a crosstab query  –  The values in these types of query are calculated, and therefore cannot be updated by an update query.
  • AutoNumber fields  –  By design, the values in AutoNumber fields change only when you add a record to a table.
  • Fields in unique-values queries and unique-records queries  –  The values in such queries are summarised. Some of the values represent a single record, and others represent more than one record. The update operation is not possible because it is not possible to determine what records were excluded as duplicates, and therefore not possible to update all the necessary records. This restriction applies whether you use an update query or try to update data manually by entering values in a form or a datasheet.
  • Fields in a union query  –  You cannot update data from fields in a union query because each record that appears in two or more data sources only appears once in the union query result. Because some duplicate records are removed from the results, Access cannot update all the necessary records.
  • Fields that are primary keys  –  In some cases, such as if the primary key field is used in a table relationship, you cannot update the field by using a query unless you first set the relationship to automatically cascade updates.

Note: When you cascade updates, Access automatically updates foreign key values when you change a primary key value in a parent table.

Top of Page

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.

Tip: Back up your database before you run an update query. You cannot undo the results of an update query, and making a backup helps make sure that you can reverse your changes if you change your mind.

How to back up your database

  1. Select the File Tab, and then select Save As. Note, if you’re using Access 2010, click the File tab, and then click Save & Publish
  2. On the right, under Advanced, pick Back Up Database.
  3. In the Save Backup As dialogue box, specify a name and location for the backup copy, and then press Save. Access closes the original file, creates a backup, and then reopens the original file. To revert to a backup, exit and rename the original file so that the backup copy can use the name of the original version. Transfer 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. The query designer opens, and the Show Table dialogue box opens.
  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.A table in the query designer1. Table shown in the query designer2. 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.A query with all table 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 criteriaThe following table shows some example criteria and explains the effect that they have on the results of a query.

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

CriteriaEffect
>234Returns 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() – 30Uses 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 NullReturns all records that contain a null (blank or undefined) value.
Is Not NullReturns 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.
  1. On the Design tab, in the Results group, press Run.
  2. Accept that the query returns the records that you want to update.
  3. To delete any fields that you do not want included in the query design, select the fields and then press DELETE.
  4. To create 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 Design tab, in the Query Type group, choose Update. This procedure teaches you how to edit a select query to an update query. Once you do this, Access imports 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.
An update query with a single update criterion
  1. 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.

ExpressionResult
“Salesperson”In a Text field, changes a text value to Salesperson.
#8/10/20#In a Date/Time field, changes a date value to 10-Aug-20.
YesIn 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.5Increases 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.
  1. On the Design tab, in the Results group, select Run. An alert message appears.
  2. To run the query and update the data, click Yes.

Note: 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. Design an update query and add both the source and destination tables to the query.
  2. Connect those tables on the fields that contain the related information.
  3. Append the names of your destination fields to the Field row of the query design grid.
  4. Insert 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.

The Clients table

Client IDNameAddressCityState/ProvincePostal CodeCountry/RegionPhoneContact
1Baldwin Museum of Science1 Main St.New YorkNY12345USA(505) 555-2122Josh Barnhill
2Blue Yonder Airlines52 1st St.BostonMA23456USA(104) 555-2123Waleed Heloo
3Coho Winery3122 75th Ave. S.W.SeattleWA34567USA(206) 555-2124Pica Guido
4Contoso Pharmaceuticals1 Contoso Blvd.LondonBucksNS1 EW2England(171) 555-2125Zoltan Harmuth
5Fourth CoffeeCalle Smith 2Mexico City56789Mexico(7) 555-2126Julian Price
6Consolidated Messenger3123 75th St. S.SeattleWA34567USA(206) 555-2125Miles Reid
7Graphic Design Institute1587 Office PkwyTampaFL87654USA(916) 555-2128Tzipi Butnaru
8Litware, Inc.3 Microsoft WayPortlandOR31415USA(503) 555-2129Brian Smith
9Tailspin Toys4 Microsoft WayPortlandOR31415USA(503) 555-2233Phil Gibbins

The Customers table

Customer IDNameAddressCityState/ProvincePostal CodeCountry or RegionPhoneManager
1Baldwin Museum of Science1 Main St.New YorkNY12345USA(505) 555-2122Steve Riley
2Blue Yonder Airlines52 1st St.BostonMA23456USA(104) 555-2123Waleed Heloo
3Coho Winery3122 75th Ave. S.W.SeattleWA34567USA(206) 555-2124Pica Guido
4Contoso Pharmaceuticals1 Contoso Blvd.LondonBucksNS1 EW2England(171) 555-2125Zoltan Harmuth
5Fourth CoffeeCalle Huevos 134Mexico City56789Mexico(7) 555-2126Julian Price
6Consolidated Messenger3123 75th St. S.SeattleWA34567USA(206) 555-2125Christine Hughes
7Graphic Design Institute67 Big St.TampaFL87654USA(916) 555-2128Dana Birkby
8Litware, Inc.3 Microsoft WayPortlandOR31415USA(503) 555-2129Jesper Aaberg
9Tailspin Toys4 Microsoft WayPortlandOR31415USA(503) 555-2233Phil Gibbins

As you continue, remember that although the data types for each table field do not have to match, they must be compatible. Access must be able to convert the data in the source table into a type that the destination table can use. In some cases, the conversion process might delete some data. For more information about restrictions when you convert data types, see the section Restrictions on data type conversions.

Create and run the update query

Note: The following steps assume the use of the two preceding sample tables. You can adapt the steps to fit your data.

  1. On the Create tab, in the Queries group, pick Query Design
  2. In the Show Table dialogue box, select the Tables tab.
  3. Double-click your source and destination tables to append them to the query, and then press Close. All tables emerge in a window in the query designer.
  4. In most cases, Access instantly links related fields in a query. To manually join fields that include related information, drag the related field from one table to the associated field in the other table. For example, if you apply the sample tables displayed before, you drag the Client ID field to the Customer ID field. Access forms a relationship between those fields in the two tables and employs that relationship to join any related records.
  5. On the Design tab, in the Query Type group, select Update.
  6. In the destination table, double-click the fields that you want to update. Every field displays in the Field row in the query design grid. If you use the sample tables, you create every field except the Customer ID field. Realise that the name of the destination table emerges in the Table row of the design grid.
  7. In the Update To row of the query, in all of the columns that has a destination field, append the name of the source table and the field in the source table that matches with the field in the destination table, and ensure that you use this syntax: [Table].[Field], where you contain table and field names with square brackets, and you split table and field names with a full stop. This figure denotes some of the design grid, applying the sample tables. Pay attention to the syntax for the table and field names in the Update To row.
A query that updates one table with data from another

As you proceed, keep in mind that you must spell the table and field names in the Update To row accurately, and you must replicate any punctuation in the initial table and field names. However, you can take mirroring capitalisation lightly.

  1. On the Design tab, in the Results group, choose Run.
  2. Once asked to verify the update, press 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.

Convert to this typeFrom this typeChanges or restrictions
TextMemoAccess deletes all but the first 255 characters.
NumberNo restrictions.
Date/TimeNo restrictions.
CurrencyNo restrictions.
AutoNumberNo restrictions.
Yes/NoThe value -1 (Yes in a Yes/No field) converts to Yes. The value 0 (No in a Yes/No fields) converts to No.
HyperlinkAccess truncates links longer than 255 characters.
MemoTextNo restrictions.
NumberNo restrictions.
Date/TimeNo restrictions.
CurrencyNo restrictions.
AutoNumberNo restrictions.
Yes/NoThe value -1 (Yes in a Yes/No field) converts to Yes. The value 0 (No in a Yes/No fields) converts to No.
HyperlinkNo restrictions.
NumberTextText must consist of numbers, valid currency, and decimal separators. The number of characters in the Text field must fall within the size set for the Number field.
MemoThe Memo field must contain only text and valid currency and decimal separators. The number of characters in the Memo field must fall within the size set for the Number field.
Number, but with a different field size or precisionValues must not be larger or smaller than what the new field size can store. Changing precision might cause Access to round some values.
Date/TimeThe dates that you can convert depend on the size of the number field. Remember that Access stores all dates as serial dates, and it stores the date values as double-precision floating integers.Access uses December 30, 1899 as date 0. Dates outside the range April 18, 1899 and September 11, 1900 exceed the size of a Byte field. Dates outside the range April 13, 1810 and September 16, 1989 exceed the size of an Integer field.

To accommodate all possible dates, set the Field Size property of your Number field to Long Integer or greater.
CurrencyValues must not exceed (or fall below) the size limit set for the field. For example, you can convert a Currency field to an Integer field only when those values are greater than 255 and do not exceed 32,767.
AutoNumberValues must fall within the size limit set for the field.
Yes/No“Yes” values convert to -1. “No” values convert to 0.
Date/TimeTextOriginal text must be a recognizable date or date/time combination. For example, 18-Jan-2020.
MemoOriginal text must be a recognizable date or date/time combination. For example, 18-Jan-2020.
NumberValue must fall between -657,434 and 2,958,465.99998843.
CurrencyValue must fall between -$657,434 and $2,958,465.9999.
AutoNumberValue must exceed -657,434 and be less than 2,958,466.
Yes/NoThe value -1 (Yes) converts to December 29, 1899. The value 0 (No) converts to midnight (12:00 AM).
CurrencyTextText must consist of numbers and valid separators.
MemoText must consist of numbers and valid separators.
NumberNo restrictions.
Date/TimeNo restrictions, but Access might round the value
AutoNumberNo restrictions.
Yes/NoThe value -1 (Yes) converts to $1, and the value 0 (No) converts to 0$.
AutoNumberTextNot allowed if the AutoNumber field serves as a primary key.
MemoNot allowed if the AutoNumber field serves as a primary key.
NumberNot allowed if the AutoNumber field serves as a primary key.
Date/TimeNot allowed if the AutoNumber field serves as a primary key.
CurrencyNot allowed if the AutoNumber field serves as a primary key.
Yes/NoNot allowed if the AutoNumber field serves as a primary key.
Yes/NoTextOriginal text must consist only of Yes, No, True, False, On, or Off.
MemoOriginal text must consist only of Yes, No, True, False, On, or Off.
NumberZero or Null converts to No, all other values convert to Yes.
Date/TimeNull or 12:00:00 AM converts to No, all other values convert to Yes.
CurrencyZero and Null convert to No, all other values convert to Yes.
AutoNumberAll values convert to Yes.
HyperlinkTextIf the original text contains a valid Web address, such as adatum.com, www.adatum.com, or http://www.adatum.com&nbsp;, Access converts the text to a hyperlink. Access tries to convert other values, meaning that you see underlined text, and the cursor changes when you point at the link, but the links don’t work. The text can contain any valid Web protocol, including http://, gopher://, telnet://, ftp://, and wais://.
MemoSee the previous entry. The same restrictions apply.
NumberNot allowed when a Number field is part of a relationship. If the original value is in the form of a valid Internet Protocol (IP) address (four number triplets separated by a period: nnn.nnn.nnn.nnn) and the numbers happen to coincide with a Web address, the conversion results in a valid link. Otherwise, Access appends http:// to the beginning of each value, and the resulting links are not valid.
Date/TimeAccess appends http:// to the beginning of each address, but the resulting links will almost never work.
CurrencyAccess appends http:// to the beginning of each value, but like dates, the resulting links will almost never work.
AutoNumberNot allowed when the AutoNumber field is part of a relationship. Access appends http:// to the beginning of each value, but the resulting links will almost never work.
Yes/NoAccess converts all Yes values to -1 and all No values to 0, and appends http:// to the beginning of each value. The resulting links do not work.

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.

Syntax

UPDATE table     SET newvalue     WHERE criteria;

The UPDATE statement has the following parts:

PartDescription
tableThe name of the table that contains the data that you want to modify.
newvalueAn expression that determines the value to be inserted into a particular field in the updated records.
criteriaAn expression that determines which records will be updated. Only records that satisfy the expression are updated.

Remarks

The UPDATE statement is especially useful when you want to change many records or when the records that you want to change are in multiple tables.

You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom:

UPDATE Orders

SET OrderAmount = OrderAmount * 1.1,

Freight = Freight * 1.03

WHERE ShipCountry = ‘UK’;

Top of Page

Leave a Reply

%d bloggers like this: