How To Update Query In Access

You apply update queries in Access databases to add, change, or delete the information in an existing record. You lot can think of update queries as a powerful form of the Find and Supercede dialog box. You cannot utilize an update query to add new records to a database, or to delete records from a database.

To add new records to a database you apply an append query, and to delete whole records from a database you use a delete query.

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 non depend on the value that you want to replace.
Update lots of records in i pass.
Change records in more than one table at the aforementioned time.

Restrictions on fields that can be updated

An update query cannot be used to update data in the post-obit types of fields:

Calculated fields The values in calculated fields do non permanently reside in tables. They only be in your computer'southward temporary memory after Access calculates them. Because calculated fields exercise not have a permanent storage location, y'all 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 summarized. Some of the values represent a single record, and others represent more than 1 record. The update functioning is non possible considering information technology is non possible to determine what records were excluded as duplicates, and therefore non possible to update all the necessary records. This restriction applies whether you employ an update query or endeavor to update data manually by inbound values in a form or a datasheet.
Fields in a wedlock query You cannot update data from fields in a wedlock query because each record that appears in two or more data sources only appears once in the spousal relationship query event. Because some indistinguishable 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 human relationship, y'all cannot update the field by using a query unless you get-go set the human relationship to automatically cascade updates.

Notation:When you pour updates, Access automatically updates foreign key values when yous change a principal key value in a parent table.

Top of Page

Using an update query

As a all-time practice when creating an update query, kickoff create a select query that identifies the records that you desire to update, and and then catechumen that query to an update query that you lot can run to update the records. By selecting data first, yous can verify that you're updating the records you want before you actually change whatever data.

Tip:Back upward your database before y'all 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 lot change your mind.

How to back up your database

Click the File Tab, then click Save Equally. Notation, if you're using Access 2010, click the File tab, and then click Salve & Publish.
On the right, under Avant-garde, click Support Database.
In the Save Backup As dialog box, specify a proper noun and location for the backup copy, and then click Salvage.

Access closes the original file, creates a fill-in, 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 proper name of the original version to the backup copy, and then open the renamed fill-in copy in Access.

In this section

Step ane: Create a select query to identify the records to update
Step two: Update the records

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

Open the database that contains the records you desire to update.
On the Create tab, in the Queries group, click Query Blueprint.
Click the Tables tab.
Select the tabular array or tables that comprise the records that you desire to update, click Add together, and then click Close.

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

1. Tabular array shown in the query designer

2. Query design filigree
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 cavalcade in the query design grid.

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

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 upshot that they accept on the results of a query.

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

Criteria	Outcome
>234	Returns all numbers greater than 234. To observe 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 ane-December-17 (ANSI-89). If your database uses the ANSI-92 wildcard characters, utilise single quotation marks (') instead of pound signs (#). Example: Between '2/2/2020' And '12/ane/2020' .
Not "Germany"	Finds all records where the exact contents of the field are non exactly equal to "Federal republic of germany." The criterion will render 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 pct 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 ready, use the percent sign (%) instead of the asterisk (*).
In(Canada,UK)	In a list, finds all records containing Canada or U.k..
Similar "[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, utilise 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 terminal letter of the alphabet is unknown. If your database uses the ANSI-92 wildcard graphic symbol ready, utilise the underscore (_) instead of the question mark (?).
#ii/2/2020#	Finds all records for Feb 2, 2020. If your database uses the ANSI-92 wildcard grapheme set, surroundings the date with single quotation marks (') instead of pound signs (#); for example, ('ii/ii/2020').
< Date() - 30	Uses the Date function to render all dates more than 30 days sometime.
Date()	Uses the Date function to return all records containing today's date.
Between Date() And DateAdd("Thousand", 3, Date())	Uses the Date and DateAdd functions to render all records between today'southward date and 3 months from today's date.
Is Null	Returns all records that contain a goose egg (bare or undefined) value.
Is Not Zip	Returns all records that incorporate a value.
""	Returns all records that contain a zero-length cord. You utilize zero-length strings when you lot need to add a value to a required field, but you don't all the same know what that value is. For instance, a field might crave a fax number, simply some of your customers might not have fax machines. In that instance, you enter a pair of double quotation marks with no space betwixt them ("") instead of a number.

On the Design tab, in the Results group, click Run.
Verify that the query returns the records that you want to update.
To remove whatever fields that y'all do not want included in the query design, select the fields and and then press DELETE.
To add any fields that y'all want to include in the query design, drag the additional fields to the query design grid.

Step 2: Update the records

On the Design tab, in the Query Type grouping, click Update.

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

Locate the field that contains the data that you desire to change, then blazon your expression (your change criteria) in the Update to row for that field.

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

Table of instance expressions

This tabular array shows some example expressions and explains how they alter data.

Expression	Effect
"Salesperson"	In a Text field, changes a text value to Salesperson.
#viii/10/20#	In a Date/Time field, changes a date value to 10-Aug-twenty.
Yes	In a Yes/No field, changes a No value to Yes.
"PN" & [PartNumber]	Adds "PN" to the kickoff of each specified part number.
[UnitPrice] * [Quantity]	Multiplies the values in fields named UnitPrice and Quantity.
[Freight] * one.5	Increases the values in a field named Freight past 50 percent.
DSum("[Quantity] * [UnitPrice]", "Gild Details", "[ProductID]=" & [ProductID])	Where the ProductID values in the current tabular array match the ProductID values in tabular array named Guild 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 information technology tin can operate confronting more one table and table field.
Right([ShipPostalCode], v)	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 zip (0) value in a field named UnitPrice.

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

An alert message appears.
To run the query and update the data, click Yes.

Note:When y'all run the query, you lot might notice that some fields are missing from your outcome set. If your query contains fields that yous don't update, Access does non brandish those fields in the results, by default. For instance, y'all 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 1 table to some other

When y'all need to update data from one table to another, consider the post-obit rule: the data types for the source and destination fields must either match or be uniform.

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

The process of updating information from 1 tabular array to some other follows these broad steps:

Create an update query and add both the source and destination tables to the query.
Join those tables on the fields that contain the related information.
Add the names of your destination fields to the Field row of the query design grid.
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 utilise of 2 similar tables. In this example, the Clients table is located in a database that you just inherited, and information technology contains more recent data than the Customers table. You can see that some of the manager names and addresses accept changed. For that reason, you determine to update the Customers table with the data from the Clients tabular array.

The Clients table

Client ID	Proper noun	Address	Urban center	State/Province	Postal Code	Country/Region	Telephone	Contact
i	Baldwin Museum of Science	one Chief St.	New York	NY	12345	USA	(505) 555-2122	Josh Barnhill
two	Blue Yonder Airlines	52 1st St.	Boston	MA	23456	USA	(104) 555-2123	Waleed Heloo
3	Coho Winery	3122 75th Ave. S.W.	Seattle	WA	34567	USA	(206) 555-2124	Pica Guido
iv	Contoso Pharmaceuticals	one Contoso Blvd.	London	Bucks	NS1 EW2	England	(171) 555-2125	Zoltan Harmuth
5	4th Coffee	Calle Smith 2	Mexico City		56789	Mexico	(vii) 555-2126	Julian Price
6	Consolidated Messenger	3123 75th St. Southward.	Seattle	WA	34567	U.s.	(206) 555-2125	Miles Reid
7	Graphic Blueprint Found	1587 Office Pkwy	Tampa	FL	87654	United states of america	(916) 555-2128	Tzipi Butnaru
eight	Litware, Inc.	3 Microsoft Way	Portland	OR	31415	Usa	(503) 555-2129	Brian Smith
9	Tailspin Toys	iv Microsoft Fashion	Portland	OR	31415	United states	(503) 555-2233	Phil Gibbins

The Customers table

Customer ID	Name	Address	Urban center	Land/Province	Postal Code	Land or Region	Telephone	Manager
i	Baldwin Museum of Science	1 Main St.	New York	NY	12345	USA	(505) 555-2122	Steve Riley
2	Blue Yonder Airlines	52 1st St.	Boston	MA	23456	USA	(104) 555-2123	Waleed Heloo
3	Coho Winery	3122 75th Ave. S.W.	Seattle	WA	34567	U.s.	(206) 555-2124	Pica Guido
4	Contoso Pharmaceuticals	one Contoso Blvd.	London	Bucks	NS1 EW2	England	(171) 555-2125	Zoltan Harmuth
5	Fourth Coffee	Calle Huevos 134	United mexican states City		56789	United mexican states	(vii) 555-2126	Julian Price
six	Consolidated Messenger	3123 75th St. S.	Seattle	WA	34567	The states	(206) 555-2125	Christine Hughes
7	Graphic Design Plant	67 Big St.	Tampa	FL	87654	U.s.a.	(916) 555-2128	Dana Birkby
8	Litware, Inc.	iii Microsoft Fashion	Portland	OR	31415	United states	(503) 555-2129	Jesper Aaberg
9	Tailspin Toys	4 Microsoft Way	Portland	OR	31415	USA	(503) 555-2233	Phil Gibbins

As you go along, think that although the data types for each table field exercise not have to friction match, they must exist compatible. Access must be able to convert the data in the source table into a type that the destination tabular array can employ. In some cases, the conversion process might delete some information. For more information about restrictions when yous catechumen data types, see the section Restrictions on data type conversions.

Create and run the update query

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

On the Create tab, in the Queries grouping, click Query Design.
Select Add Tables (Show Table in Access 2013).
Double-click your source and destination tables to add them to the query. Each table appears in a window in the query designer.
In most cases, Admission automatically joins related fields in a query. To manually bring together fields that contain related information, elevate the related field from ane 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 human relationship betwixt those fields in the two tables and uses that relationship to bring together any related records.
On the Design tab, in the Query Type grouping, click Update.
In the destination table, double-click the fields that you lot want to update. Each field appears in the Field row in the query design grid.

If y'all use the sample tables, you add all fields except the Client ID field. Detect that the name of the destination tabular array appears in the Table row of the pattern filigree.
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 tabular array that corresponds to the field in the destination table, and make sure that yous use this syntax: [ Table ].[ Field ], where yous enclose table and field names with foursquare brackets, and you lot separate tabular array and field names with a catamenia.

This figure shows function of the blueprint grid, using the sample tables. Annotation the syntax for the table and field names in the Update To row.

As you continue, remember that y'all 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 exercise non have to lucifer capitalization.
On the Design tab, in the Results group, click Run.
When asked to confirm the update, click Yes.

Restrictions on data blazon conversions

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

Convert to this blazon	From this type	Changes or restrictions
Text	Memo	Access deletes all simply the first 255 characters.
	Number	No restrictions.
	Date/Time	No restrictions.
	Currency	No restrictions.
	AutoNumber	No restrictions.
	Aye/No	The value -1 (Yep in a Yes/No field) converts to Aye. The value 0 (No in a Yes/No fields) converts to No.
	Hyperlink	Access truncates links longer than 255 characters.
Memo	Text	No restrictions.
	Number	No restrictions.
	Date/Time	No restrictions.
	Currency	No restrictions.
	AutoNumber	No restrictions.
	Yes/No	The value -1 (Yes in a Yes/No field) converts to Yes. The value 0 (No in a Yep/No fields) converts to No.
	Hyperlink	No restrictions.
Number	Text	Text must consist of numbers, valid currency, and decimal separators. The number of characters in the Text field must fall inside the size gear up for the Number field.
	Memo	The Memo field must contain just text and valid currency and decimal separators. The number of characters in the Memo field must fall inside the size set for the Number field.
	Number, simply with a different field size or precision	Values must non be larger or smaller than what the new field size tin can store. Changing precision might crusade Access to round some values.
	Date/Fourth dimension	The dates that yous tin convert depend on the size of the number field. Call up that Admission stores all dates as series dates, and it stores the appointment values equally double-precision floating integers. Access uses December 30, 1899 as date 0. Dates outside the range April eighteen, 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 belongings of your Number field to Long Integer or greater.
	Currency	Values must not exceed (or fall below) the size limit prepare for the field. For example, you lot tin convert a Currency field to an Integer field but when those values are greater than 255 and practise not exceed 32,767.
	AutoNumber	Values must fall inside the size limit set for the field.
	Yes/No	"Yes" values catechumen to -1. "No" values convert to 0.

Engagement/Fourth dimension	Text	Original text must be a recognizable date or appointment/time combination. For example, 18-Jan-2020.
	Memo	Original text must be a recognizable appointment or date/time combination. For case, 18-January-2020.
	Number	Value must fall between -657,434 and 2,958,465.99998843.
	Currency	Value must autumn between -$657,434 and $2,958,465.9999.
	AutoNumber	Value must exceed -657,434 and exist less than ii,958,466.
	Yes/No	The value -1 (Yes) converts to December 29, 1899. The value 0 (No) converts to midnight (12:00 AM).

Currency	Text	Text must consist of numbers and valid separators.
	Memo	Text must consist of numbers and valid separators.
	Number	No restrictions.
	Date/Time	No restrictions, merely Access might circular the value
	AutoNumber	No restrictions.
	Yep/No	The value -1 (Yes) converts to $1, and the value 0 (No) converts to 0$.

AutoNumber	Text	Not immune if the AutoNumber field serves as a primary key.
	Memo	Not allowed if the AutoNumber field serves as a main key.
	Number	Not immune if the AutoNumber field serves as a primary key.
	Date/Time	Not allowed if the AutoNumber field serves every bit a primary key.
	Currency	Not immune if the AutoNumber field serves as a primary key.
	Yes/No	Not immune if the AutoNumber field serves as a primary key.

Yeah/No	Text	Original text must consist just of Yes, No, True, False, On, or Off.
	Memo	Original text must consist only of Yes, No, Truthful, Simulated, On, or Off.
	Number	Cypher or Zilch converts to No, all other values convert to Yes.
	Date/Fourth dimension	Null or 12:00:00 AM converts to No, all other values convert to Yes.
	Currency	Zippo and Null convert to No, all other values convert to Yes.
	AutoNumber	All values convert to Yeah.

Hyperlink	Text	If the original text contains a valid Spider web accost, such as adatum.com, world wide web.adatum.com, or http://www.adatum.com , Access converts the text to a hyperlink. Access tries to convert other values, pregnant that you run into underlined text, and the cursor changes when you betoken at the link, but the links don't work. The text tin can contain any valid Web protocol, including http://, gopher://, telnet://, ftp://, and wais://.
	Memo	Encounter the previous entry. The same restrictions apply.
	Number	Not allowed when a Number field is part of a relationship. If the original value is in the class 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 Spider web address, the conversion results in a valid link. Otherwise, Access appends http:// to the beginning of each value, and the resulting links are non valid.
	Date/Time	Admission appends http:// to the starting time of each address, merely the resulting links will about never work.
	Currency	Access appends http:// to the beginning of each value, only similar dates, the resulting links will nearly never piece of work.
	AutoNumber	Not 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/No	Access converts all Yes values to -1 and all No values to 0, and appends http:// to the first of each value. The resulting links do not work.

Top of Folio

Cease Disabled Mode from blocking a query

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

This action or event has been blocked by Disabled Way.

Past default, Access disables all action queries (update, suspend, delete, or brand 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 Bulletin Bar.

Tiptop of Page

SQL version: UPDATE statement

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

This department presents the syntax for and an instance of an UPDATE statement.

Syntax

UPDATE table Set newvalue WHERE criteria;

The UPDATE statement has the following parts:

Part	Description
table	The proper noun of the table that contains the data that y'all want to alter.
newvalue	An expression that determines the value to be inserted into a particular field in the updated records.
criteria	An expression that determines which records will be updated. Only records that satisfy the expression are updated.

Remarks

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

You lot can change several fields at the aforementioned time. The post-obit example increases the Order Amount values by ten pct and the Freight values by three percent for shippers in the United Kingdom:

UPDATE Orders

SET OrderAmount = OrderAmount * one.one,

Freight = Freight * i.03

WHERE ShipCountry = 'UK';

Top of Page