How To Change A Table Name And Update Queries In Access

You lot employ update queries in Access databases to add, change, or delete the data in an existing record. You lot can recall of update queries equally a powerful grade of the Discover and Replace dialog box. You lot cannot use an update query to add together new records to a database, or to delete records from a database.

To add new records to a database you use 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 Supplant and an update query:

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

Different the Find and Replace dialog box, an update query lets y'all:

Employ criteria that do not depend on the value that you want to replace.
Update lots of records in one pass.
Modify records in more than ane table at the same fourth dimension.

Restrictions on fields that can be updated

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

Calculated fields The values in calculated fields practice non permanently reside in tables. They just exist in your computer'due south temporary memory after Access calculates them. Because calculated fields do not accept a permanent storage location, you lot 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 past an update query.
AutoNumber fields Past design, the values in AutoNumber fields change merely 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 one tape. The update functioning is not possible because information technology is not possible to determine what records were excluded equally duplicates, and therefore not possible to update all the necessary records. This brake applies whether you use an update query or try to update information manually by entering values in a form or a datasheet.
Fields in a union query Yous cannot update data from fields in a wedlock query because each record that appears in two or more than data sources simply 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 principal keys In some cases, such as if the primary key field is used in a tabular array relationship, y'all cannot update the field past using a query unless you commencement set the relationship to automatically cascade updates.

Note:When you pour updates, Access automatically updates strange fundamental values when y'all modify a primary key value in a parent table.

Top of Page

Using an update query

As a best practise when creating an update query, first create a select query that identifies the records that yous want to update, and then convert that query to an update query that you lot tin run to update the records. By selecting information get-go, you can verify that you lot're updating the records yous want earlier you actually change whatsoever data.

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

How to back upward your database

Click the File Tab, and then click Save As. Note, if y'all're using Admission 2010, click the File tab, and then click Save & Publish.
On the right, under Avant-garde, click Back Upward Database.
In the Salve Backup Every bit dialog box, specify a name and location for the fill-in re-create, and and then click Save.

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 and so that the backup copy can apply the name of the original version. Assign the name of the original version to the fill-in re-create, and so open the renamed backup copy in Admission.

In this section

Footstep ane: 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

Open the database that contains the records you want to update.
On the Create tab, in the Queries group, click Query Pattern.
Click the Tables tab.
Select the table or tables that contain the records that y'all want to update, click Add together, and and then click Close.

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

1. Table shown in the query designer

2. Query design grid
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 tin can add one table field per column in the query design filigree.

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

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

Tabular array of example criteria

The post-obit 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 tabular array use wildcard characters to brand the query more flexible or powerful.

Criteria	Upshot
>234	Returns all numbers greater than 234. To find all numbers less than 234, utilise < 234.
>="Cajhen"	Returns all records from Cajhen through the end of the alphabet.
Between #two/2/2020# And #12/1/2020#	Returns dates from ii-Feb-17 through 1-Dec-17 (ANSI-89). If your database uses the ANSI-92 wildcard characters, utilise single quotation marks (') instead of pound signs (#). Example: Between 'ii/2/2020' And '12/i/2020' .
Not "Frg"	Finds all records where the exact contents of the field are not exactly equal to "Germany." The benchmark volition return records that comprise characters in addition to "Germany," such as "Germany (euro)" or "Europe (Deutschland)".
Non "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 set up, use the percent sign (%) instead of the asterisk (*).
In(Canada,U.k.)	In a list, finds all records containing Canada or Uk.
Like "[A-D]*"	In a text field, finds all records that commencement with the letters A through D. If your database uses the ANSI-92 wildcard character ready, utilise the percent sign (%) instead of the asterisk (*).
Like "ar"	Finds all records that include the letter of the alphabet sequence "ar". If your database uses the ANSI-92 wildcard character set, employ the per centum 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 iv letters are "Dewe" and the concluding letter is unknown. If your database uses the ANSI-92 wildcard character set, apply the underscore (_) instead of the question marking (?).
#two/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, ('two/two/2020').
< Appointment() - 30	Uses the Engagement function to return all dates more xxx days old.
Date()	Uses the Engagement role to return all records containing today's engagement.
Between Date() And DateAdd("Chiliad", 3, Appointment())	Uses the Engagement and DateAdd functions to render all records between today's date and three months from today'southward appointment.
Is Naught	Returns all records that contain a null (blank or undefined) value.
Is Non Null	Returns all records that incorporate a value.
""	Returns all records that contain a nothing-length string. Yous employ zero-length strings when you demand to add a value to a required field, but yous don't yet know what that value is. For example, a field might require a fax number, only some of your customers might non take fax machines. In that instance, yous enter a pair of double quotation marks with no infinite betwixt them ("") instead of a number.

On the Design tab, in the Results grouping, click Run.
Verify that the query returns the records that y'all desire to update.
To remove whatsoever fields that you do not want included in the query blueprint, select the fields and then press DELETE.
To add any fields that y'all want to include in the query blueprint, elevate the additional fields to the query pattern grid.

Step 2: Update the records

On the Blueprint tab, in the Query Blazon grouping, click Update.

This procedure shows you how to change a select query to an update query. When you do this, Admission adds the Update to row in the query design filigree. The following illustration shows an update query that returns all the avails purchased subsequently January 5, 2005 and changes the location to "Warehouse 3" for all the records that run across that criterion.

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

Y'all can utilise any valid expression in the Update to row.

Table of example expressions

This tabular array shows some case expressions and explains how they change data.

Expression	Result
"Salesperson"	In a Text field, changes a text value to Salesperson.
#8/10/20#	In a Date/Fourth dimension field, changes a appointment value to ten-Aug-20.
Aye	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.five	Increases the values in a field named Freight past 50 percent.
DSum("[Quantity] * [UnitPrice]", "Order Details", "[ProductID]=" & [ProductID])	Where the ProductID values in the current table match the ProductID values in table named Club Details, this expression updates sales totals by multiplying the values in a field named Quantity past the values in a field named UnitPrice. The expression uses the DSum function because information technology can operate against more than than ane tabular array and table field.
Right([ShipPostalCode], 5)	Truncates (removes) the leftmost characters in a text or numeric string and leaves the five rightmost characters.
IIf(IsNull([UnitPrice]), 0, [UnitPrice])	Changes a zero (unknown or undefined) value to a zero (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 information, click Yes.

Note:When yous run the query, you might observe that some fields are missing from your issue set. If your query contains fields that you don't update, Access does non display those fields in the results, by default. For example, you might include ID fields from ii 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.

Tiptop of Folio

Update data from one tabular array to another

When you lot need to update data from 1 tabular array to some other, consider the following rule: the data types for the source and destination fields must either friction match or be compatible.

Furthermore, when you update data from 1 tabular array to some other and use compatible data types instead of matching data types, Admission converts the data types of those fields in the destination table. Equally a result, some of the data in the destination fields may be truncated (deleted). The section Restrictions on data type conversions lists the means in which yous can and cannot convert data types. The table in this section also explains when converting a information 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 tabular array to another 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 comprise the related data.
Add the names of your destination fields to the Field row of the query design grid.
Add together the names of your source fields to the Update To row of the query design grid past using the following syntax: [source_table].[source_field].

The steps in this department assume the utilise of two similar tables. In this example, the Clients table is located in a database that y'all just inherited, and it contains more recent information than the Customers table. Y'all can see that some of the manager names and addresses accept inverse. For that reason, you decide to update the Customers tabular array with the data from the Clients table.

The Clients table

Client ID	Name	Address	City	Land/Province	Postal Code	Country/Region	Phone	Contact
ane	Baldwin Museum of Science	1 Main St.	New York	NY	12345	USA	(505) 555-2122	Josh Barnhill
2	Blue Yonder Airlines	52 1st St.	Boston	MA	23456	Us	(104) 555-2123	Waleed Heloo
iii	Coho Winery	3122 75th Ave. S.W.	Seattle	WA	34567	U.s.	(206) 555-2124	Pica Guido
4	Contoso Pharmaceuticals	1 Contoso Blvd.	London	Bucks	NS1 EW2	England	(171) 555-2125	Zoltan Harmuth
5	Quaternary Java	Calle Smith 2	Mexico City		56789	Mexico	(7) 555-2126	Julian Price
6	Consolidated Messenger	3123 75th St. S.	Seattle	WA	34567	Us	(206) 555-2125	Miles Reid
7	Graphic Pattern Institute	1587 Part Pkwy	Tampa	FL	87654	Us	(916) 555-2128	Tzipi Butnaru
8	Litware, Inc.	iii Microsoft Way	Portland	OR	31415	USA	(503) 555-2129	Brian Smith
nine	Tailspin Toys	4 Microsoft Way	Portland	OR	31415	The states	(503) 555-2233	Phil Gibbins

The Customers table

Client ID	Proper name	Address	Metropolis	State/Province	Postal Code	Country or Region	Phone	Manager
1	Baldwin Museum of Science	1 Master St.	New York	NY	12345	U.s.a.	(505) 555-2122	Steve Riley
two	Blueish Yonder Airlines	52 1st St.	Boston	MA	23456	Us	(104) 555-2123	Waleed Heloo
3	Coho Winery	3122 75th Ave. S.W.	Seattle	WA	34567	USA	(206) 555-2124	Pica Guido
4	Contoso Pharmaceuticals	i Contoso Blvd.	London	Bucks	NS1 EW2	England	(171) 555-2125	Zoltan Harmuth
5	Fourth Coffee	Calle Huevos 134	Mexico Metropolis		56789	Mexico	(vii) 555-2126	Julian Price
half-dozen	Consolidated Messenger	3123 75th St. South.	Seattle	WA	34567	U.s.a.	(206) 555-2125	Christine Hughes
7	Graphic Pattern Establish	67 Big St.	Tampa	FL	87654	USA	(916) 555-2128	Dana Birkby
8	Litware, Inc.	iii Microsoft Manner	Portland	OR	31415	United states of america	(503) 555-2129	Jesper Aaberg
9	Tailspin Toys	iv Microsoft Way	Portland	OR	31415	USA	(503) 555-2233	Phil Gibbins

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

Create and run the update query

Note:The post-obit 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 group, click Query Design.
Select Add together Tables (Show Table in Access 2013).
Double-click your source and destination tables to add them to the query. Each tabular array appears in a window in the query designer.
In most cases, Admission automatically joins related fields in a query. To manually join fields that contain related data, drag the related field from one table to the equivalent field in the other table.

For example, if you lot employ the sample tables shown earlier, you drag the Client ID field to the Customer ID field. Admission creates a human relationship between those fields in the 2 tables and uses that relationship to join any related records.
On the Blueprint tab, in the Query Type group, click Update.
In the destination table, double-click the fields that you want to update. Each field appears in the Field row in the query design filigree.

If you apply the sample tables, you lot add all fields except the Customer ID field. Observe that the proper name of the destination table appears in the Table row of the design grid.
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 brand sure that yous utilise this syntax: [ Table ].[ Field ], where yous enclose tabular array and field names with foursquare brackets, and you split table and field names with a period.

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

Equally yous keep, 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 lot do not have to match capitalization.
On the Pattern tab, in the Results group, click Run.
When asked to ostend the update, click Yes.

Restrictions on data type conversions

The post-obit tabular array lists the data types that Access provides, explains whatever restrictions on how to convert information types, and briefly describes any data loss that might occur during conversion.

Convert to this type	From this type	Changes or restrictions
Text	Memo	Access deletes all but the kickoff 255 characters.
	Number	No restrictions.
	Date/Time	No restrictions.
	Currency	No restrictions.
	AutoNumber	No restrictions.
	Yeah/No	The value -1 (Yes in a Yep/No field) converts to Yes. The value 0 (No in a Yep/No fields) converts to No.
	Hyperlink	Access truncates links longer than 255 characters.
Memo	Text	No restrictions.
	Number	No restrictions.
	Engagement/Time	No restrictions.
	Currency	No restrictions.
	AutoNumber	No restrictions.
	Yes/No	The value -i (Yes in a Aye/No field) converts to Yes. The value 0 (No in a Yes/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 within the size prepare for the Number field.
	Memo	The Memo field must comprise just 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 precision	Values must non be larger or smaller than what the new field size can store. Changing precision might cause Access to round some values.
	Appointment/Fourth dimension	The dates that you can convert depend on the size of the number field. Recollect that Admission stores all dates as series dates, and it stores the date values every bit double-precision floating integers. Access uses Dec 30, 1899 as date 0. Dates outside the range Apr 18, 1899 and September 11, 1900 exceed the size of a Byte field. Dates outside the range April xiii, 1810 and September sixteen, 1989 exceed the size of an Integer field. To arrange all possible dates, set the Field Size property of your Number field to Long Integer or greater.
	Currency	Values must not exceed (or fall beneath) the size limit set for the field. For example, you lot can catechumen a Currency field to an Integer field only when those values are greater than 255 and exercise not exceed 32,767.
	AutoNumber	Values must autumn inside the size limit set up for the field.
	Yep/No	"Yes" values convert to -1. "No" values catechumen to 0.

Appointment/Time	Text	Original text must exist a recognizable date or date/fourth dimension combination. For example, 18-January-2020.
	Memo	Original text must exist a recognizable date or date/time combination. For case, eighteen-Jan-2020.
	Number	Value must autumn between -657,434 and 2,958,465.99998843.
	Currency	Value must fall betwixt -$657,434 and $2,958,465.9999.
	AutoNumber	Value must exceed -657,434 and be less than two,958,466.
	Yes/No	The value -ane (Yes) converts to Dec 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.
	Appointment/Time	No restrictions, merely Access might round the value
	AutoNumber	No restrictions.
	Yes/No	The value -ane (Yeah) converts to $1, and the value 0 (No) converts to 0$.

AutoNumber	Text	Not allowed if the AutoNumber field serves as a primary primal.
	Memo	Non allowed if the AutoNumber field serves every bit a chief key.
	Number	Not immune if the AutoNumber field serves as a chief fundamental.
	Appointment/Time	Not allowed if the AutoNumber field serves as a chief key.
	Currency	Non allowed if the AutoNumber field serves as a primary key.
	Yep/No	Non immune if the AutoNumber field serves every bit a primary key.

Yep/No	Text	Original text must consist simply of Yeah, No, True, False, On, or Off.
	Memo	Original text must consist but of Yep, No, Truthful, False, On, or Off.
	Number	Cipher or Zippo converts to No, all other values convert to Yes.
	Date/Time	Null or 12:00:00 AM converts to No, all other values convert to Yes.
	Currency	Zero 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 Web address, such as adatum.com, www.adatum.com, or http://www.adatum.com , Access converts the text to a hyperlink. Admission tries to catechumen other values, meaning that you see underlined text, and the cursor changes when yous point at the link, but the links don't work. The text tin can incorporate whatever valid Web protocol, including http://, gopher://, telnet://, ftp://, and wais://.
	Memo	See 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 form of a valid Internet Protocol (IP) address (4 number triplets separated by a catamenia: 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 non valid.
	Appointment/Time	Access appends http:// to the outset of each address, but the resulting links will nearly never work.
	Currency	Access appends http:// to the kickoff of each value, simply like dates, the resulting links volition well-nigh never piece of work.
	AutoNumber	Not allowed when the AutoNumber field is part of a human relationship. Access appends http:// to the outset of each value, but the resulting links will nearly never piece of work.
	Yes/No	Admission converts all Aye values to -1 and all No values to 0, and appends http:// to the start of each value. The resulting links exercise not work.

Top of Page

Stop Disabled Style from blocking a query

If yous try to run an activity query and it seems similar nothing occurs, check the Access condition 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 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 deportment, y'all tin can notwithstanding enable the query for the electric current database session past 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 employ SQL view, create a blank, new query, and so 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 post-obit parts:

Part	Description
table	The proper noun of the table that contains the data that you desire to modify.
newvalue	An expression that determines the value to exist inserted into a item field in the updated records.
criteria	An expression that determines which records will be updated. But records that satisfy the expression are updated.

Remarks

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

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

UPDATE Orders

SET OrderAmount = OrderAmount * 1.1,

Freight = Freight * 1.03

WHERE ShipCountry = 'Uk';

Superlative of Page