Access update query

Access update query DEFAULT

MS Access 2007: Update Query that updates values in one table with values from another table

totn Access

This MSAccess tutorial explains how to create an Update query that updates values in one table with values from another table in Access 2007 (with screenshots and step-by-step instructions).

See solution in other versions of Access:

Question: In Microsoft Access 2007, I'm trying to update about 6000+ records from one table with values from another table, but I'm having problems. Actually I am trying to update the Bot table MFG with the Big table MFG when Big.PART = Bot.PART.

The query I'm using is:

UPDATE Bot SET Bot.MFG = (SELECT Big.MFG FROM Big WHERE Big.PART = Bot.PART) WHERE EXISTS (SELECT Big.MFG FROM Big WHERE Big.PART = Bot.PART);

Could you please help?

Answer: First, you need to create a new query. To do this, select the Create tab in the toolbar at the top of the screen. Then click on the Query Design button in the Other group.

Microsoft Access

When the Show Table window appears, select the tables that you wish to use in the query and click on the Add button. When you have finished adding your tables, click on the Close button.

In this example, we've selected the Big and Bot tables. You can select multiple tables by holding down the key while selecting the table names.

Microsoft Access

Next, right-click somewhere next to the tables (but not on a table) in the query editor, and select Query Type > Update Query from the popup menu.

Microsoft Access

Next, build the query like the one below:

Microsoft Access

This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match.

The SQL for this query is as follows:

UPDATE Big INNER JOIN Bot ON Big.PART = Bot.PART SET Bot.MFG = [Big].[MFG];
Sours: https://www.techonthenet.com/access/queries/update2_2007.php

Use a Field in One Table to Update a Field in Another Table

You’ve imported a table that contains updated prices for some of the records in a table in your database. The data in all the other fields in the existing table is still correct. Is there any way—short of using a complex VBA procedure—to update the price data in the existing table based on the updated prices from the imported table without overwriting any of the other fields in the existing table?

You probably already know that you can use an update query to update the values of fields in a table, but did you know that you can use an update query to update the values in one table with the values from another? This solution will show you how to do just that. If you can join the two tables on some common field or combination of fields, you can use an update query to update a field in one table based on the values found in a second table.

Here are the steps to create an update query that updates values across tables:

  1. Create a standard Select query. Add the two tables to the query and join them on the common field or fields. In the sample database, we added the tblAlbums and tblAlbumsUpdated tables to the query. We will refer to tblAlbumsUpdated as the source table because it will supply the values to be used to update the other table; tblAlbums is the target table because it will be the target of the updates. Access has automatically joined the two tables on AlbumID. If the name of the common field is not the same, you will have to join the two tables by dragging the common field from one table to the other.

  2. Select Query → Update to change the type of query to an update action query.

  3. Drag the field to be updated in the target table to the query grid. In the Update To cell for the field that will be updated, specify the fully qualified name of the field in the source table that will be the source of the updated values. This field name should include the name of the table surrounded by square brackets, a period, and the name of the field surrounded by square brackets. For qryUpdateAlbumPrices, drag the AlbumID field from tblAlbums to the query grid. The field settings for AlbumID are shown in Table 1-4.

    Table 1-4. Field settings for qryUpdateAlbumPrices

    Field

    Table

    Update To

    Criteria

    AlbumID

    tblAlbums

    [tblAlbumsUpdated].[PurchasePrice]

    Is Null

    Warning

    Be careful when specifying the Update To value. If you misspell the source field name, you run the risk of changing the values to the misspelled string rather than to the values in the source field. If Access surrounds the Update To value with quotes or prompts you for an unexpected parameter when you attempt to execute the update query, it’s likely that you made a spelling mistake.

  4. Optionally specify criteria to limit the rows to be updated. In the qryUpdateAlbumPrices example, we used criteria to limit the updated rows to those with null (missing) prices (see Table 1-4). This prevents Access from overwriting any existing non-null values in tblAlbums.

  5. Execute the query by selecting Query → Run or by clicking on the exclamation point icon.

    Tip

    You can preview the selected records in an action query by choosing View → Datasheet or by clicking on the Datasheet icon. The query will not be run, but you’ll be able to see which records would be updated had you run the query.

For an example of updating a field in a table based on the value of a field in another table, open the tblAlbums table found in the database. Note that most of the purchase prices are null (see Figure 1-15). Open tblAlbumsUpdated, and you’ll see that many of the purchase prices for the same albums have been entered (see Figure 1-16).

Figure 1-15. Many of the purchase values in tblAlbums are null

Figure 1-16. tblAlbumsUpdated contains updated purchase prices for several albums in tblAlbums

Now run the qryUpdateAlbumPrices query found in the same database (see Figure 1-17). This action query will take the PurchasePrice values from tblAlbumsUpdated and copy it into the Purchase Price field in tblAlbums for each record where the two AlbumID fields match and the price value in tblAlbums is currently null. When the query is finished, open tblAlbums again—you should see that the Purchase Price field in this table has been updated based on the values in tblAlbumsUpdated (see Figure 1-18).

Figure 1-17. The qryUpdateAlbumPrices update query in design view

Figure 1-18. The updated purchase prices for albums in tblAlbums

You can use update queries in Access to update the values in a target table, and you can use another table to supply the values for the update. The trick is to join the two tables using a common field and to properly specify the name of the field from the source table in the Update To cell.

You can update more than one field at a time in an update query. You can also include additional fields in the query grid to further limit the rows to be updated. Drag these additional fields to the query grid and specify criteria for them. As long as you leave the Update To row blank for these columns, they will be used for their criteria only and will not be updated. Update queries are the most efficient way to make bulk changes to data; they are much more efficient than using a recordset in a VBA procedure.

Get Access Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Sours: https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s06.html
  1. Fig garden loop
  2. Ej and gina
  3. Best january dividend stocks
  4. Terraform ecs scheduled task
  5. Jinja2 newline in string

You use update queries in Access databases to add, change, or delete the information in an existing record. You can think of update queries as a powerful form of the Find and Replace dialog box. You cannot use 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 use an append query, and to delete whole records from a database you use a delete query.

In this article

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.

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 summarized. 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. Click the File Tab, and then click Save As. Note, if you're using Access 2010, click the File tab, and then click Save & Publish

  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

  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.

    A table in the query designer

    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.

    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 criteria

    The 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.

    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 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 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.

    An update query with a single update 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 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 Design tab, in the Results group, click Run.

    An alert message appears.

  4. 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. 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.

The Clients table

Client ID

Name

Address

City

State/Province

Postal Code

Country/Region

Phone

Contact

1

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

USA

(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

1 Contoso Blvd.

London

Bucks

NS1 EW2

England

(171) 555-2125

Zoltan Harmuth

5

Fourth Coffee

Calle Smith 2

Mexico City

56789

Mexico

(7) 555-2126

Julian Price

6

Consolidated Messenger

3123 75th St. S.

Seattle

WA

34567

USA

(206) 555-2125

Miles Reid

7

Graphic Design Institute

1587 Office Pkwy

Tampa

FL

87654

USA

(916) 555-2128

Tzipi Butnaru

8

Litware, Inc.

3 Microsoft Way

Portland

OR

31415

USA

(503) 555-2129

Brian Smith

9

Tailspin Toys

4 Microsoft Way

Portland

OR

31415

USA

(503) 555-2233

Phil Gibbins

The Customers table

Customer ID

Name

Address

City

State/Province

Postal Code

Country or Region

Phone

Manager

1

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

USA

(206) 555-2124

Pica Guido

4

Contoso Pharmaceuticals

1 Contoso Blvd.

London

Bucks

NS1 EW2

England

(171) 555-2125

Zoltan Harmuth

5

Fourth Coffee

Calle Huevos 134

Mexico City

56789

Mexico

(7) 555-2126

Julian Price

6

Consolidated Messenger

3123 75th St. S.

Seattle

WA

34567

USA

(206) 555-2125

Christine Hughes

7

Graphic Design Institute

67 Big St.

Tampa

FL

87654

USA

(916) 555-2128

Dana Birkby

8

Litware, Inc.

3 Microsoft Way

Portland

OR

31415

USA

(503) 555-2129

Jesper Aaberg

9

Tailspin Toys

4 Microsoft Way

Portland

OR

31415

USA

(503) 555-2233

Phil 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, click Query Design

  2. Select Add Tables (Show Table in Access 2013).

  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 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.

    A query that updates one table with data from another

    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 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.

Convert to this type

From this type

Changes or restrictions

Text

Memo

Access deletes all but the first 255 characters.

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 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 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 set for the Number field.

Memo

The 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 precision

Values must not be larger or smaller than what the new field size can store. Changing precision might cause Access to round some values.

Date/Time

The 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.

Currency

Values 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.

AutoNumber

Values must fall within the size limit set for the field.

Yes/No

"Yes" values convert to -1. "No" values convert to 0.

Date/Time

Text

Original text must be a recognizable date or date/time combination. For example, 18-Jan-2020.

Memo

Original text must be a recognizable date or date/time combination. For example, 18-Jan-2020.

Number

Value must fall between -657,434 and 2,958,465.99998843.

Currency

Value must fall between -$657,434 and $2,958,465.9999.

AutoNumber

Value must exceed -657,434 and be less than 2,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, but Access might round the value

AutoNumber

No restrictions.

Yes/No

The value -1 (Yes) converts to $1, and the value 0 (No) converts to 0$.

AutoNumber

Text

Not allowed if the AutoNumber field serves as a primary key.

Memo

Not allowed if the AutoNumber field serves as a primary key.

Number

Not allowed if the AutoNumber field serves as a primary key.

Date/Time

Not allowed if the AutoNumber field serves as a primary key.

Currency

Not allowed if the AutoNumber field serves as a primary key.

Yes/No

Not allowed if the AutoNumber field serves as a primary key.

Yes/No

Text

Original text must consist only of Yes, No, True, False, On, or Off.

Memo

Original text must consist only of Yes, No, True, False, On, or Off.

Number

Zero or Null 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 Yes.

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. 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://.

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 (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/Time

Access appends http:// to the beginning of each address, but the resulting links will almost never work.

Currency

Access appends http:// to the beginning of each value, but like dates, the resulting links will almost never 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 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:

Part

Description

table

The name of the table that contains the data that you want to modify.

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 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

Sours: https://support.microsoft.com/en-us/office/create-and-run-an-update-query-9dddc97c-f17d-43f4-a729-35e5ee1e0514

Microsoft Access Update Query Examples, SQL Syntax, and Errors

There are several ways to use Update Queries:

Update the Same Value for All Records

In this example, all invoices with a balance due that are 30 days late have the LateStatus field updated to True.

UPDATE tblInvoices SET [LateStatus] = True WHERE (([Balance] > 0) AND ([InvoiceDate] < Date()-30)

Clear Values from a Field

In this example, a date field is emptied by setting it to Null:

UPDATE tblTasks SET [DueDate] = Null

Update Using its Own Value

In this example, the Contacted field is incremented by one if the record has Called = True. Each contact may have a different value in its Contacted field depending on how many times it was contacted in the past.

UPDATE tblContact SET [Contacted] = [Contacted] + 1 WHERE [Called]

Update with a Value from the Same Record

In this example, an employee's FirstDay field is set to the same value as the HireDate.

UPDATE tblEmployee SET [FirstDay] = [HireDate]

Update with a Expression from Values in the Same Record

In this example, the invoice's GrandTotal field becomes the sum of its Total and Tax fields.

UPDATE tblInvoices SET [GrandTotal] = [Total] + [Tax]

Update with Values from Fields in Another Table

In this example, the address records are updated with the City and State value from a zip code lookup table. If the record has a zip code that matches the lookup table, its City and State fields are replaced.

UPDATE tblAddress INNER JOIN tblZipCodes ON [tblAddress].[ZipCode] = [tblZipCodes].[ZipCode] SET [tblAddress].[City] = [tblZipCodes].[City], [tblAddress].[State] = [tblZipCodes].[State] WHERE ([Country] = 'US')

Update with a VBA Function

Any built-in VBA function that returns a value can be used as the update value. In this example, the Country field is converted to all upper case using the VBA UCase command.

UPDATE tblAddress SET [Country] = UCase([Country]) WHERE ([Country] Is Not Null)

Here's another example of using a VBA function to Convert a Field to Proper Case with a Microsoft Access Update Query

Note: This doesn't work if you're in an ADP since those queries run on SQL Server which doesn't know VBA syntax.


Update with Your Own VBA Function

Any public function that you create in a standard module can be used in a query. Your field is updated with the return value of the function. If your function takes parameters, you can pass field values as parameters and the function executes for each record that is processed.

UPDATE tblAddress SET [MyField] = MyCoolFunction([Field1], [Field2])

Note: This doesn't work if you're in an ADP since those queries run on SQL Server which can't "see" your modules or functions.

Sours: http://www.fmsinc.com/MicrosoftAccess/query/snytax/update-query.html

Update query access

UPDATE statement (Microsoft Access SQL)

  • 2 minutes to read

Applies to: Access 2013, Office 2013

Creates an update query that changes values in fields in a specified table based on specified criteria.

Syntax

UPDATE table SET newvalue WHERE criteria;

The UPDATE statement has these parts:

table

The name of the table containing the data you want to modify.

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.

UPDATE 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 does not generate a result set. Also, after you update records using an update query, you cannot undo the operation. If you want to know which records were updated, first examine the results of a select query that uses the same criteria, and then run the update query.
  • Maintain backup copies of your data at all times. If you update the wrong records, you can retrieve them from your backup copies.

Example

This example changes values in the ReportsTo field to 5 for all employee records that currently have ReportsTo values of 2.

Sours: https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/update-statement-microsoft-access-sql
Microsoft Access: How to Use the Update Query

An Update query is a type of action query that makes changes to several records at the same time. For example, you could create an Update query to raise prices on all the products in a table by 10%.

Just like other action queries, you create an Update query by first creating a Select query and then converting the Select query to an Update query.

  1. Click the Create tab on the ribbon.
  2. Click the Query Design button.
    Update Queries

    The Query Design window and Show Table dialog box both appear. Here you must select the tables and/or queries you want to use in the Update query.

  3. Double-click the tables and queries you want to add and click Close.
    Update Queries

    Now you need to convert the Select query to an Update query.

  4. Click the Update button.

    Access converts the Select query to an Update query. Notice an Update To row appears in the design grid. Now you should specify the fields you want to update.

  5. The next step can be a little bit tricky—you need to tell Access which fields to update and how to update them. For example, if you wanted to raise the price of the Normal Price field by 10 percent you would write an expression—like [Normal Price]+([Normal Price]*.1)—in the field's Update To row to make this happen.

  6. Click the Update To row for the field you want to update and type an expression.

    Now run the query.

  7. Click the Run button.
    Update Queries

    Access lets you know how many records will be affected and that the changes can't be undone by the Undo command.

  8. Click Yes.
    Update Queries

Access updates the records.

Sours: https://www.customguide.com/access/update-queries

Similar news:

Updating Records in Microsoft Access with an Update Query:

A situation may arise where we need to update many records in a database table when certain information changes or needs to be modified.

A Microsoft Access Update Query updates specified values in a table for all records or for those records that match a specified criteria.

It is possible to update records manually in a database table, either via a form or through the tables datasheet, however this may take a very long time. Changing records manually is not only time consuming, it is also inefficient, and lends itself to errors as you update the records.

The best way to handle this type of event, the updating of many records in a database table, is to use an Update Query to make the changes to data in one operation. You will save yourself time, and eliminate the possibility of manual errors.

Note: Due to the permanent effects of working with an update action query, you should always make a backup copy of your tables, or your database before attempting this option.

How to create a Microsoft Access Update Query:

  1. Create a SELECT query to determine the records that will be updated. Apply any required query criteria, and view the data that will be updated by pressing the Datasheet button.
  2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Update Query.
  3. After you are satisfied that the information to be updated is correct Run the query using the Run Icon

Now consider the following scenario example, that will illustrate the use of a Microsoft Access Update query:

One of your product suppliers has decided to increase the price of their products by 3 percent (%) across the board. You have decided that you will use an Update Query to change all of the relevant records in your Products table.

Our original table appears like the following example:

The original Products table before we run the Update Query
The original Products table before we run the Update Query

We need to update the Unit Price field, to update all records from our supplier (Exotic Liquids) as they have informed us of a 3% price increase.

Creating the Update Query

Here are the steps that we follow to produce the required outcome:

  1. Create a new query using the Products table and the Suppliers table. Include the fields that you are going to use to update the data (ProductID, ProductName and UnitPrice from the Products table, and CompanyName from the Suppliers table)

    We have also included criteria in the CompanyName field to limit the results to only those of the Supplier that we are updating the records for.

    Also, in the image below, you will see that we have included an additional field, just to test our expression. This will give us a value for the 3% increase, just to check that the results will be returned correctly. This field will be removed before we run the update, but we will use the expression later.

    Creating a SELECT query, that will later be changed to the UPDATE Query
    Creating a SELECT query, that will later be changed to the UPDATE Query
  2. To check what results this will produce, run the query by clicking on the datasheet Datasheet view button

    Checking the results before running the Update Query
    Checking the results before running the Update Query
  3. When we are happy with the resulting data, we can switch back to design view to convert the query to an Update query.

    From the Query Type button Query Type Button on the toolbar, select Update Query Update Query Option

    In the curUnitPrice column, in the Update To cell, type in the expression [curUnitPrice]*1.03 and press enter. This expression will update the original Unit Price by 3 Percent.

    The query design should now look like:

    The Update Query design
    The Update Query design
  4. We now need to Run this query, using the Run button The Run button to run the action query to update the data in our Products table that meets the criteria that is applied. The warning dialog box indicates the number of records that will be update, click Yes to accept this:

    The warning message, warning that you are going to update records from the original database table.

Remember that the update query will permanently update records from the specified table(s), therefore it is very important that you have backed up the table(s) or database before running this object.

Check Your Update Query Results

Once you have ran the update query, you can check the results by once again changing the update query back to a select query.

Sours: http://www.databasedev.co.uk/update_query.html


2871 2872 2873 2874 2875