Change existing data by using an update query

In this topic...

Overview
An optional section. Provides background information about update queries, including when to use them and when to use other tools, such as a delete query. The section also provides links to topics that explain how to enter and edit data manually, and how to use other types of action queries, such as append and make-table queries.

Prerequisites for running update queries
An optional section. Provides reminders about essential facts, such as the types of fields that you cannot update by running a query, and explains how to perform common tasks, such as starting the Northwind Traders sample database and backing up a database.

Use the Query Designer
Explains how to start and use the Query Designer, the essential tool for creating update queries.

Update data in one or more fields
Explains the basics of creating and running an update query against one or more fields in a single table. The section also explains how to test your update criteria by first creating a select query, and then converting that select query to an update query.

Use data from multiple tables to create an update query
Explains how to build an update query that uses fields from two or more tables.

Change data in multiple tables
Explains how to create an update query that changes data in more than one table. Subsections explain how improper query design can produce unwanted results.

Update the data in one table by using data from another table
Explains how to use an update query that takes data from one table and writes it to records in another table.

Various ways to use update queries
Individual sections explain how to perform a variety of tasks: How to "blank out" fields by setting them to Null, how to add data to Null fields, how to use parameters in connection with update queries, and how to use wildcard characters in your select and update queries.

Overview

Microsoft Office Access 2003 provides several ways to change the data in a database. For example, you can open a table or the results of a select query in Datasheet view — a row and column grid similar to a Microsoft Office Excel 2003 worksheet — and change your data manually. But what do you do when you need to change a large number of records, especially when those records must meet one or more criteria? You use an update query.

If it helps, you can think of update queries as a more powerful version of the Find and Replace dialog box. Your update queries contain select criteria (the equivalent of a search string) and update criteria (the equivalent of a replacement string). The query finds all records that match your select criteria, and then writes your update criteria to those records, all in one pass.

However, your select and update criteria can be much more powerful than ordinary search and replacement strings, because your criteria can contain expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) and calculations. For example, your select criteria could find all products that have a discount of 20 percent or greater, and your update criteria could change those discounts to a different percentage that you specify.

In addition, your update queries can use multiple select and update criteria. To continue the previous example, you could find all products that have discounts of 20 percent, 18 percent, or 15 percent, and reduce these discounts to 15 percent, 12 percent, and 10 percent, respectively — something that you cannot do with other tools.

This topic explains how to use update queries to perform a variety of tasks, such as changing Yes values to No values, inserting data into blank fields, and changing values in more than one table simultaneously. The how-to steps do not assume that you're familiar with update queries, and they provide any expressions that are needed to accomplish the task being demonstrated. As you proceed, remember that an update query is not the same thing as what is called an updatable query. An updatable query is another type of query (a select query) that displays its results in a datasheet, and that allows you to change the data in that datasheet manually. In contrast, update queries do not display a datasheet when they finish running. For more information about select queries, see the topic Create a select or crosstab query (MDB).

 Note   Remember that you use update queries only on existing data. If you want to insert new records or delete whole records, you must use different query types or other tools. The following links take you to information about the other query types and the other tools that Access provides for adding, removing, and updating or changing the data in a database.

  • If you aren't comfortable with using update queries, you can use the Find and Replace dialog box. You use this dialog box when you need to update a small number of records, and when you need to find records in which the search or replace criteria match simple patterns. For information about using the Find and Replace dialog box, see the topic Find and replace part or all of a record in an Access table, query, or form.
  • If you want to update data manually by using a data entry form or a datasheet, see the topic Add, edit, and delete data. You can change data manually by first locating a record by navigating to it in a form, and then editing the fields. You can also open a table or a query in Datasheet view (a row and column grid similar to an Excel worksheet) and change the data directly in a field.
  • If you want to copy records from one table to another, you must create an append query. For more information, see the topic Create an append query (MDB).
  • If you want to delete entire records from a database, you must create a delete query (but remember that if you want to remove only part of a record, you must use an update query). For more information, see the topic Delete one or more records from an Access database.
  • If you want to create a table from the records that are returned by another query, you can create a make-table query. For more information, see the topic Create a table from another table with a query (MDB).

Top of Page Top of Page

Prerequisites for running update queries

The following sections provide background information about update queries and queries in general. They also explain how to perform related tasks, such as backing up a database (always a good thing to do before you update a large number of records).

ShowUnderstand joins

By default, when you create an update query that uses data from more than one table, you use joins. Explaining joins is beyond the scope of this topic, so if you need information about them, see the topic About joining tables or queries in a query (MDB).


ShowTypes of fields that you cannot update

By default, you cannot change values in the following types of fields:

  • Fields that result from calculations. The values in calculated fields do not permanently reside in tables. They only exist in your computer's temporary memory after they have been calculated. Because calculated fields do not have a permanent storage location, you cannot update them.
  • Fields that use totals queries or crosstab queries as their record source.
  • AutoNumber fields. By design, the values in AutoNumber fields change only when you add a record to a table. However, you can reset AutoNumber fields under certain conditions, such as when you remove a record. For information about resetting AutoNumber fields, see How to reset an AutoNumber field value in Access in the Microsoft Knowledge Base.
  • Primary keys that participate in relationships, unless you set the relationships to automatically cascade updates through the key field and any related fields. When you cascade updates, Access automatically updates any foreign key values in a child table (the table on the many side of a one-to-many relationship) when you change a primary key value in a parent table (the table on the one side of a one-to-many relationship).

 Note   You cannot cascade updates for records that have primary key values that are set to the AutoNumber data type. For information about turning on and off cascading updates, see the section Turn on cascading updates in this article.

  • Fields in union queries.
  • Fields in unique-values queries and unique-records queries.

This rule applies when you use an update query, and when you try to update data manually by entering values in a form or a datasheet.


ShowTurn on cascading updates

The preceding section mentioned that you cannot update the values in primary key fields unless you first turn on cascading updates. You allow cascading updates by setting an option for the relationships between two tables. The following steps explain how to allow cascading updates:

  1. Open the database that contains the tables for which you want to cascade updates, and display the Database window for that database.

If you've already opened the database, close any open tables. You cannot edit relations between open tables.

  1. On the Tools menu, click Relationships Button Image.

The Relationships window appears, displaying the tables in the database and showing the relationships between them.

  1. Select and then right-click the relation that you want to change, and then click Edit Relationship.

The Edit Relationships dialog box appears.

Edit Relationships dialog box with existing relationship

  1. Make sure that the Enforce Referential Integrity check box is selected, select the Cascade Update Related Fields check box, and then click OK.

For more information about using the Edit Relationships dialog box, see the topic Define relationships between tables (MDB). For more information about defining relationships between tables, see the topic About relationships in an Access database (MDB).


ShowBack up your database

You cannot undo the changes that an update query makes to your data. For that reason, it is strongly advised that you back up your database before you run an update query. The following steps explain how to back up a database, and how to restore a table by exporting it from the backup copy to the original database:

  1. Open the database that you want to back up, and close any splash screens.
  2. On the File menu in Access, click Back Up Database.

The Save Backup As dialog box appears.

  1. Click the Save in list box, and then choose a location for the backup copy of your database.
  2. In the File name box, you can accept the name that Access provides, or you can enter another name.
  3. Click Save.

 Note   If you use an older version of Access (97, 2000, or 2002), you can use the conventional methods provided by Microsoft Windows to copy your Access database. For example, you can start Windows Explorer, locate and copy the database file, and then paste the copy into a backup location.

Once you back up or copy your database, you can undo any unwanted changes by exporting tables from your backup copy to your original copy. The following steps apply to Access 97 and later:

  1. Open the backup copy of your database.
  2. As needed, close any splash screens and switchboards or other forms, and display the Database window.
  3. Under Objects, click Tables, and then highlight the table that you want to export from the backup copy to your original database.
  4. On the File menu, click Export.
  5. Browse to your original database, highlight that database file, and then click Export.

The Export dialog box appears.

  1. To export both the table structure and the data in the table, select Definition and Data, click OK, and then click Yes to confirm the export.

The export process replaces the table in the original database with the table from your backup copy.


ShowUse expressions in your queries

You can use any valid expression in an update query. An expression is a combination of operators, field names, functions, literals, and constants that evaluates to a single value. Expressions can specify criteria (such as Order Amount>10000) or perform calculations on field values (such as Price*Quantity).

The how-to steps in this topic provide the expressions that you need to complete each specific task. However, expressions are a very powerful tool, and explaining them in detail is beyond the scope of this topic. For more examples of expressions, see the topic Examples of expressions used in update queries.


ShowOpen the Northwind Traders sample database

The how-to sections in this article use the Northwind Traders sample database. The steps assume that you have the database open in the following state:

  1. On the Help menu in Access, point to Sample Databases, and then click Northwind Sample Database.

If the Security Warning dialog box appears, click Open.

  1. Click OK to close the Northwind Traders splash screen.
  2. On the Main Switchboard, click Display Database Window.

ShowView the SQL statement behind an update query

When you use the Query Designer to create a query, Access writes a Structured Query Language (SQL) statement for that query. You can view and edit that SQL statement whenever you have a query open in Design view. The following steps explain how to view the SQL statement for a given query:

  1. In the Database window, under Objects, click Queries.
  2. Highlight the query that you want to view, and then click Design Button image.

The Query Designer starts, and then loads the query.

  1. On the View menu, click SQL View.

The SQL statement appears in an editing window.

  1. If you're comfortable writing queries in SQL, edit the query as needed.

For more information about SQL, see the topic About SQL queries (MDB).

  1. To leave SQL view, on the View menu, click Design View.

Top of Page Top of Page

Use the Query Designer

The how-to sections in this topic use the Query Designer to create and modify select queries and update queries. The sections instruct you to run select queries before you run your update queries, because running select queries can ensure that you're updating the correct records. The following steps explain how to start the Query Designer, add tables to a query, and move table fields to the design grid:

  1. Open the Northwind Traders sample database, display the Database window, and select the table (or one of the tables) that you want to use in your query.
  2. On the main toolbar, click the arrow next to the New Object Button image button, and then click Query in the list.

The New Query dialog box appears.

 Note   Access "remembers" the type of object that you select from the list. The next time you click the New Object button, Access automatically creates that type of object again. In other words, after you follow this step, Access continues to start the New Query dialog box until you choose another type of object from the list.

  1. Select Design View, and then click OK.

The Query Designer appears, and because you selected a table in step 1, that table appears in the upper pane of the Query Designer, which saves some time.

  1. In the upper pane of the Query Designer, select the fields that you want to use in your query, and then drag them to the query design grid.

You can press CTRL while you select each field, and then drag the fields to the design grid simultaneously.

Update queries can change data in more than one table, and you often need to query multiple tables to ensure that you're working with the correct records. Follow these steps to add tables to a query:

  1. On the Query Design toolbar, click Show Table Button image.

The Show Table dialog box appears.

  1. Select the table that you want to add to your query, and then click Add.

Alternatively, double-click the table.

  1. After you add the table to the design grid, click Close to close the Show Table dialog box.
  2. As needed, repeat step 4 in the preceding procedure to add table fields to the design grid.

The steps in the how-to sections assume that you have the Query Designer open, and they tell you which tables and fields to add to a given query.

Top of Page Top of Page

Update data in one or more fields

The steps in these sections explain how to change values in one or more fields in a single table. Remember that you can not undo the results of an update query, so backing up your database beforehand is a very good idea. For information about backing up a database, see the section Back up your database earlier in this topic.

The steps in this section use the Northwind Traders sample database, but you can adapt the steps for use with your own data. For information about opening and using the sample database, see the section Open the Northwind Traders sample database, also earlier in this topic.

 Note   The safest way to use an update query is to first create a select query that tests your selection criteria. For example, say that you want to update a series of Yes/No fields from No to Yes for a given customer. To do so, you add criteria to your select query until it returns all of the desired No records for that customer. When you're sure that you have the correct records, you convert your select query to an update query, enter your update criteria, and then run the query to update the selected values. Whenever possible, the how-to sections in this topic begin with select queries, and then convert those queries to update queries.

  1. Create a new query that uses the Orders table in the Northwind Traders sample database.

For information about creating a query and adding the table, see the section Use the Query Designer earlier in this topic.

  1. In the upper pane of the Query Designer, select the OrderID, ProductID, and Discount fields, and drag them to the query design grid.

You can hold down CTRL while you select each field, and then drag the fields to the design grid simultaneously.

  1. In the Criteria field below Discount, type >0.2

Remember to enter the decimal point, because you're dealing with a percentage value.

That value returns all products that have a discount rate greater than 20 percent.

  1. Click Run Button image.

If you're using the Northwind Traders database, Access returns 154 records.

When you're sure you have the data that you want to change, convert your select query to an update query:

  1. Click Design Button image to return the query to Design view.
  2. On the Query menu, click Update Query.

Access converts your select query to an update query, and adds the Update To field to the Query Designer.

  1. In the Update To field under Discount, type the desired value — in this case, 0.22

Leave the original expression (>.20) in the Criteria field. If you remove it, the update query will change every record in the table, including those that have no discount applied.

 Note   To change the data in more than one field, enter update criteria for each of the fields that you want to change. You can specify update criteria for some or all the fields in the query design grid. Keep in mind that your update criteria must match the data type assigned to the fields that you want to update. For example, you can't replace the value in a Yes/No field with text.

  1. To run the query and update the records, click Run Button image.
  2. After you run the update query and are alerted to confirm the operation, click Yes.

Access runs the query and updates the records, but you won't see the datasheet with the updated data yet.

  1. To see the results of your update query, click View Button image on the Query Design toolbar.

Note that even though you included the OrderID and ProductID fields in the original select query, Access removed them when you ran the update operation. By default, Access removes all fields that do not contain update criteria. If you want to see the additional fields, convert your query back to a select query and run it again, but use 0.22 as your select criteria.

Top of Page Top of Page

Use data from multiple tables to create an update query

The following steps explain how to create an update query that searches for a set of products and marks them as unavailable. The steps assume that you have backed up your database. If you aren't sure how to do this, see the section Back up your database earlier in this topic.

  1. Create a new query that uses the Products and Suppliers tables in the Northwind Traders sample database.

For information about creating a query and adding the tables, see the section Use the Query Designer earlier in this topic.

  1. In the Query Designer, drag the SupplierID field from the Suppliers table to the grid, and then drag the SupplierID, ProductName and Discontinued fields from the Products table to the design grid.

For either table, you can hold down CTRL while you select each field, and then drag the fields to the design grid simultaneously.

  1. Click Run Button image.

The query returns 77 records. Scroll down the list of records, and make a note of the Supplier ID number assigned to Pavlova Ltd. You'll use that ID in the following procedure.

Next, you enter criteria to locate a single supplier. Once you locate the data that you want to change, you can convert your select query into an update query:

  1. On the main toolbar, click Design Button image to return the query to Design view.
  2. In the Criteria field under the first instance of SupplierID, type 7 (the ID for Pavlova Ltd.), and then click Run Button image to run the query again.

The query returns five records, and you can see that one product, Alice Mutton, is already discontinued.

At this point, you can manually select the Discontinued check box for each record, but assume instead that you're looking at a large number of records, and that you need to convert the existing select query into an update query:

  1. Click Design Button image to return the query to Design view again.
  2. On the Query menu, click Update Query.

Access converts your select query to an update query, and adds the Update To field to the Query Designer.

  1. In the Update To field under Discontinued, enter Yes.
  2. To run the query and update the records, click Run Button image.

When you run the query, an alert message says that Access will update all five rows, even though one of those rows already contains a Yes value.

  1. In this case, the update operation won't change the existing value, so click Yes to run the query.

If you did not want to overwrite the existing value, you could enter No in the Criteria field below the Update To field, as shown in the following illustration.

Update query with two criteria

That additional constraint forces the update query to change only those records that contain a No value.

Access runs the query and updates the records, but you won't see the datasheet with the updated data yet.

  1. To see the results of your update query, click View Button image on the Query Design toolbar.

Notice that even though you included the ProductID, ProductName, and SupplierID fields in the original select query, Access removed them when you ran the update operation. By default, Access removes all fields unless either they contain update criteria or the update operation changes them. If you want to see the data that was originally in your select query, convert your update query back to a select query, and then run it again.

Top of Page Top of Page

Change data in multiple tables

In a properly designed database (a database in third normal form), the tables share a set of one-to-many relationships. For example, in the Northwind Traders database, all data for suppliers resides in one table, and all data for products resides in another. Because a supplier can provide multiple products, a one-to-many relationship exists between the Suppliers and Products tables. If you want to change the supplier data for a given set of products, you only need to change that data on the "one" side of the relationship — in the Suppliers table.

For that reason, you don't usually need to update data in multiple tables by running an update query, although you can if you need to. For information about normal forms and database design, see the topic About designing a database.

An update query that produces unwanted results

The steps in this section show you how an update query can produce unwanted results when you change a record on the "one" side of a one-to-many relationship. In this example, you need to locate a specific order and change the name of the employee who took the order:

  1. Start a new query that uses the Orders and Employees tables in the Northwind Traders database.

For information about starting a query and adding the tables, see the section Use the Query Designer earlier in this topic.

  1. Drag the EmployeeID, FirstName, and LastName fields from the Employees table to the design grid, and then drag the EmployeeID and OrderID fields from the Orders table to the design grid.
  2. In the Criteria field under LastName, type Buchanan
  3. Under FirstName, type Steven
  4. Clear the Show check box under the EmployeeID field for the Orders table.
  5. Under OrderID, type 10248

Your query should look similar to the one in the following illustration (though your fields don't need to be in the order shown here).

.

Multiple-table select query

If you click Run Button image and test the query, it returns one record for order number 10248.

Once you successfully test your criteria in the select query, you convert it to an update query and change your data:

  1. If you tested the query, click Design Button image to return it to Design view and then, on the Query menu, click Update Query.
  2. In the Update To field, under LastName, type Stahl
  3. Under FirstName, type Annik

Your query should look like the one in the following illustration.

Update query that produces unexpected results

  1. Click Run Button image, and then click Yes when asked to confirm the update operation.

The query updates one row.

On the surface, everything about the update query seems fine. Your select query returned one record, and the update query updated that one record. The problem is, you updated the employee record on the "one" side of the relationship, and that change affected every order for that employee on the "many" side of the relationship. The following steps show how to see the impact of the change:

  1. On the Query menu, click Select Query, and then run the query again.

The query does not return any results.

  1. Click Design Button image to return the query to Design view .
  2. Replace Buchanan with Stahl and replace Steven with Annik and then run the query again.

The query returns one record, for order number 10248.

  1. Go back to Design view one last time, clear 10248 from the Criteria field, and then run the query a final time.

The query returns 42 records for Annik Stahl, and all of those records used to belong to Steven Buchanan. Even though you only wanted to change one record, you updated the wrong field in a one-to-many relationship, and your change affected all related records. The steps in the next section explain how to run an update query that produces a usable result.

An update query that produces correct results

The previous section showed you how changing a record on the "one" side of a one-to-many relationship can cause unwanted changes to a large number of records. The following steps explain how to update records in two tables on the "one" side of relationships without making that mistake. In this scenario, one of your suppliers has been sold to another company. You need to change the company name and the name of one product. Because it's a good habit to get into, you start with a select query:

  1. Start a new query that uses the Products and Suppliers tables in the Northwind Traders database.

For information on starting a query and adding the tables, see Use the Query Designer earlier in this topic.

  1. Drag the ProductID and ProductName fields from the Products table to the design grid, and then drag the SupplierID and CompanyName fields from the Suppliers table to the grid.
  2. In the Criteria field under CompanyName/Suppliers, type Exotic Liquids

Your query should look like the one in the following illustration.

Select query that tests for update query

  1. Click Run Button image to test the query.

The query returns three records.

Once you've used the select query to confirm that you're using the right criteria (Exotic Liquids, in this case), you build your update query. This update query uses the same approach as the query in the previous section — it changes records on the "one" side of one-to-many relationships. In this case, it changes a supplier name and a product name, but without affecting a large number of records:

  1. Click Design Button image to return the query to Design view and then, on the Query menu, click Update Query.
  2. In the Update To field under ProductName, type Exotic Chai
  3. In the Criteria field under ProductName, type Chai
  4. In the Update To field under CompanyName, type the new supplier name, Contoso Exotics
  5. In the Criteria field directly below CompanyName, type the old supplier name, Exotic Liquids

Your query should look like the one in the following illustration.

Update query that changes values

  1. Click Run Button image to run the query, and then, when Access asks you to confirm a change to one record, click Yes.

The query changes Exotic Liquids to Contoso Exotics in the Suppliers table (on the "one" side of the relationship), and it changes Chai (in the Products table) to Exotic Chai.

To see how changing the values in the two tables affected the data, go on to the following steps.

  1. On the Query menu, click Select to convert the query back to a select query.
  2. In the Criteria field under SupplierID, type 1 and then run the query.

Exotic Liquids is now Contoso Exotics, and Chai is now Exotic Chai. In this case, changing the CompanyName field on the "one" side of the relationship properly cascaded the change through the related records.

  1. Go back to the Database window, select the Order Details table, and then create a new select query that uses that table.
  2. Drag the OrderID and ProductID fields to the query design grid.
  3. In the Criteria field under ProductID, type 1 (the product ID for Exotic Chai), and then run the query.

You can see that every order for Chai has now changed to reflect the new product name.

Top of Page Top of Page

Update the data in one table by using data from another table

At times, you may need to update the data in one table by using data from another table. For example, say that you get a table of sales data from the Southwest sales region, and you need to update a table in a global sales database by using that new data.

Keep the following facts in mind when you update one table by using data from another table:

  • Ensure that the fields in the source table and in the target table use the same data type. For example, you can't copy dates to an OLE Object field. Fields from different tables can have names that are not identical, but their data types must match. Also, the update query will not change any of the field names in the source or destination tables.
  • If the source and target tables do not share a relationship, you need to create a join. For information about creating joins, see the topic Join tables and queries in a query (MDB). For information about creating relationships, see the topic Define relationships between tables (MDB).
  • Create a select query that tests your initial selection criteria, and then convert the select query into an update query. In the Update To fields in the update query, you use the following syntax: [tablename].[fieldname], where [tablename] is the name of the source table, and [fieldname] is the name of the field that you want to copy to the destination table.

The steps in the following sections explain how to duplicate a table, change some records in the duplicated table, join that table to its original in a query, and then run an update query that copies records. Start by copying one of the tables in the Northwind Traders database and changing some of the records. The changed records become sample data for use in your update query:

  1. In the Database window, under Objects, click Tables.
  2. Right-click the Suppliers table and then click Save As.

The Save As dialog box appears.

  1. Name the copied table Suppliers2 and ensure that the As list contains Table, then click OK to create a copy of the Suppliers table.

The copied table appears in the Database window.

  1. Open the Suppliers2 table in Datasheet view.
  2. In the first record (which has a Supplier ID of 1) change the values in the Address, City, Region, and Postal Code fields.

You can change these values to any values that you want.

  1. Do the same for the third, sixth, and ninth records, and then close the table.

You'll use the Suppliers2 table as the source table for the update query. But first, test the criteria in a select query:

  1. Create a new query that uses the Suppliers and Suppliers2 tables in the Northwind Traders database.

For information about creating a query and adding a table, see the section Use the Query Designer earlier in this topic.

The Suppliers and Suppliers2 tables do not share either a relationship or a join, so you need to create a join and build a select query.

  1. On the main toolbar, click Tools and then click Relationships.
  2. In the Suppliers2 table, click the SupplierID column, drag it to the Suppliers table, and drop it on the SupplierID column in that second table.

Access creates a join, and indicates the join with a line.

  1. In the Suppliers table, drag the SupplierID, Address, City, Region, and PostalCode fields to the design grid.
  2. In the Criteria field under SupplierID, type 1 or 3 or 6 or 9

These are the IDs of the fields that you changed in the Suppliers2 table.

  1. On the main toolbar, click View Button image to preview the query.

The records in the query should match the records that you changed in the previous section. If not, click Design Button image to return to Design view, and then adjust your criteria until they return the correct records.

Once your select query returns the correct records, you convert it to an update query and add the table and field names, using the syntax provided at the beginning of this section:

  1. On the Query menu, click Update Query to convert your select query to an update query.
  2. In the Update To fields under Address, City, Region, and PostalCode, enter the corresponding table and field names from the Suppliers2 table. Your finished query should look something like this:
           
Field: SupplierID Address City Region PostalCode
Table: Suppliers Suppliers Suppliers Suppliers Suppliers
Update To:   [Suppliers2].[Address] [Suppliers2].[City] [Suppliers2].[Region] [Suppliers2].[PostalCode]
Criteria: 1 Or 3 Or 6 Or 9         
  1. Click Run Button image to run the query.
  2. When Access asks you to confirm the update for four records, click Yes.
  3. Convert your update query back to a select query (leaving the values in the Criteria field unchanged) to see the changed records in the Suppliers table.

Top of Page Top of Page

Various ways to use update queries

The following sections demonstrate some additional ways to use update queries. The sections explain how to "blank out" a field by setting its values to Null, and how to do just the opposite — how to add values to Null or empty fields. Additional sections explain how to add or delete a few characters in a field, how to use parameters in connection with update queries, and how to use wildcard characters in your select and update queries.

"Blank out" a field (set the values in a field to Null or enter zero-length strings)

You can use an update query to "blank out" a field by setting its values to Null or by changing its values to zero-length strings. The following procedure shows how to perform both tasks.

You can set fields to Null, or enter zero-length strings, for a variety of reasons. In this exercise, one of your suppliers is changing its prices. You don't want to ship any of that supplier's products until you get the new prices, so you set the price values for that supplier's products to Null. Using null values or zero-length strings stops your database from performing calculations, which in turn stops you from completing and shipping orders. You could set the price values to zero instead, but your database can still use a zero value in calculations, so by using Null you ensure that you don't ship orders by mistake.

For more information about Null values and zero-length strings, see the following topics:

Also remember that you cannot undo the results of this or any other update query, so back up your database before you run the query. For information on doing so, see the section Back up your database earlier in this topic:

  1. Create a new query that uses the Products and Suppliers tables in the Northwind Traders database.

For information about creating a query and adding the tables, see the section Use the Query Designer earlier in this topic.

  1. Drag the SupplierID field from the Suppliers table to the design grid, and then drag the SupplierID, ProductID, ProductName, and UnitPrice fields from the Products table to the grid.

Adding the SupplierID fields from both tables gives you all of the information that you need to create your update query. As a rule, it helps to give yourself as much data as possible, because doing so can ensure greater accuracy in the results that your update query produces.

  1. Click View Button image to preview the query, or click Run Button image to run the query.

The query returns 77 records.

  1. Note the supplier ID number for Tokyo Traders (4).

Now that you have the information needed to set the correct records to Null, convert your select query to an update query and then make your changes:

  1. On the Query menu, click Update to convert the query to an update query.
  2. In the Criteria field, under SupplierID/Products, type 4
  3. In the Update To field under UnitPrice, type Null or, to enter zero-length strings, type a pair of double quotes with no space between them ("").
  4. Click Run Button image to run the query.
  5. When Access asks you to confirm the update of three records, click Yes.
  6. Convert your update query back to a select query.
  7. In the Criteria field under UnitPrice, type Is Null and then run the query.

If you entered a zero-length string, type a pair of double quotes with no space between them ("") in the Criteria field, and then run the query.

The query returns all three records for Tokyo Traders, and the UnitPrice field contains no values.

  1. Note the product ID numbers for all three products (9, 10, and 74).

Leave the query open, and go on to the next section for information about adding data to fields that contain Null values or zero-length strings.

Add data to "empty" or "blank" fields

The steps in this section explain how to find fields that contain Null values or zero-length strings, and how to add data to those fields. These steps reverse the action that you took in the previous section, and the steps assume that you have left the query from that section open.

You can add data to a field that contains Null values or zero-length strings manually, or by running an update query. The following sections explain how take both actions.

Add data to a Null field manually

  • With the query or table open in Datasheet view (the view that you had open at the end of the previous section), enter the desired value in the fields that contain Null values or zero-length strings.

For example, if you have the query open from the previous section, you can type 97 in the UnitPrice fields for Mishi Kobe Niku, 31 for Ikura, and 10 for Longlife Tofu.

 Note   You can't update some of the fields when working in Datasheet view. For more information about the fields that you can't update, see the section Types of fields that you cannot update, earlier in this topic.

Use an update query to add data to Null fields

Depending on your situation, you may find that entering data manually is faster than running an update query.

  1. Return the query from the previous section to Design view.
  2. In the Criteria field under ProductID, type 9 (the Product ID for Mishi Kobe Niku).
  3. In the Update To field under UnitPrice, type 97
  4. Click Run Button image.
  5. When Access asks you to confirm the update of one record, click Yes.
  6. Repeat steps 2 through 5, but this time type 10 in the Criteria field and 31 in the Update To field.
  7. Repeat steps 2 through 5 again, but this time type 74 in the Criteria field and 10 in the Update To field.

Those steps restore the original price values that the update query in the previous section set to Null.

Use parameters with your update queries

You can use a mix of input parameters and update queries whenever doing so makes sense. For example, you can create an input parameter that prompts users for a value, such as a product name. When the user enters the product name and runs the query, the query, in turn, can change a value associated with the product name, such as a price or a discount.

The steps in this section explain how to create a query that does just that. The query asks you to input a product name, and it then doubles the price for that product:

  1. Create a new query that uses the Products table in the Northwind Traders database.

For information about creating a query and adding a table, see the section Use the Query Designer earlier in this topic.

  1. Move the ProductName and UnitPrice fields to the grid, and then either click View Button image to preview the query, or click Run Button image to run the query.

The query returns 77 records. Note the unit price for Chai, $18.00.

 Note   If you've followed the other exercises in this topic, you may have changed Chai to Exotic Chai. If you have, keep that change in mind, along with the unit price.

Next, you convert your query to an update query, and enter a query parameter and expression that changes a unit price.

  1. On the Query menu, click Update to convert the query to an update query.
  2. In the Criteria field under ProductName, type the following parameter exactly as shown: [Enter Product Name:]
  3. In the Update To field under UnitPrice, type the following expression exactly as shown: [UnitPrice]*2

Your finished query should look something like this:

     
Field: ProductName UnitPrice
Table: Products Products
Update To:   [UnitPrice]*2
Criteria: [Enter Product Name:]  
  1. Click Run Button image to run the query.

The Enter Parameter Value dialog box appears, prompting you to enter a product name.

  1. Type Chai — or, if you changed the product name in a previous exercise, type Exotic Chai and then click OK.
  2. When Access asks you to confirm the update of one row, click Yes.

If you convert your query back to a select query and run it again, or if you open the Products table in Datasheet view, you can see that the query changed the price of Chai from $18 to $36.

Use wildcard characters in update queries

You can use wildcard characters in update queries, but remember that they work best when used in the Criteria field of the Query Designer. If you use wildcard characters in the Update To field of the Query Designer, Access treats them as literals and writes them to your database — a result that you do not want.

 Note   An in-depth explanation of how Access uses wildcard characters is beyond the scope of this topic. For more information about using wildcard characters, see the topics Find and replace part or all of a record in an Access table, query, or form and Access wildcard character reference.

Finding dates can be one of the harder problems to solve when using wildcard characters. Access stores dates as numbers, but you can apply formatting to those numbers and make the dates fit almost any cultural or geographic preference. For example, a Date/Time field may store a date as 7/14/1996, but a format can render that same date as 14-Jul-1996. In order to find dates by using wildcard characters, you sometimes need to remove any formatting.

The following exercises show you how to query for dates by using wildcard characters, and also how to build an update query from the results of the first query:

  1. Create a new query that uses the Orders and Customers tables in the Northwind Traders database.

For information about creating a query and adding the tables, see the section Use the Query Designer earlier in this topic.

  1. From the Orders table, drag the OrderID, CustomerID, OrderDate, and RequiredDate fields to the design grid.
  2. From the Customers table, drag the CustomerID field to the design grid, and drop it between the existing CustomerID and OrderDate fields.
  3. In the Criteria row, under OrderDate, type Like "*10*"

Your query should look like the one in the following illustration.

Select query that uses wildcard characters

  1. Click Run Button image to run the query.

The query returns records that contain 10, such as 10-Jul-1996, but it also returns records that don't seem to contain 10, such as 03-Oct-1997. Why? Because in the Gregorian calendar, October is the tenth month, and Access disregards the formatting when you use wildcard characters in a query.

Leaving the query open, go on to the next steps to see how removing the format can help you remove the confusion that query results can sometimes cause:

  1. Open the Orders table in Design view.
  2. Select the OrderDate field.
  3. On the General tab (at the bottom of the table design grid), clear the format from the Format field by highlighting dd-mmm-yyyy and then pressing the DELETE key.
  4. Save your changes, and then close the table design grid.
  5. Rerun the query that you created in the previous steps.

With the format removed, Access displays the values in the Order Date column in the mm/dd/yyyy format, while the date values in the Shipped Date column appear in the dd-mmm-yyyy format. Note that each of the Order Date values returned by the query contains 10. So if a query seems to return unwanted results, try removing any formatting and running the query again.

Also, you can use this approach to filter data. For example, the criterion Like "10*" returns records only for October, and Like "*10/1996" returns only orders placed on the tenth day of any month in 1996.

Once your original select query returns the correct set of records, you can then create an update query. For this example, suppose that one of your employees imported data for several orders, but set the ship dates incorrectly. The query that you created in the previous steps already provides customer ID numbers, order dates, and ship dates, so the following steps show you how to work with dates in an update query:

  1. Reopen the query in Design view.
  2. In the Criteria field under OrderDate, type Like "10/*/1997" and then run the query again.

The query returns all orders for October of 1997 — 38 records.

Your employee entered incorrect ship dates for three customers — Alfreds Futterkiste, White Clover Markets, and Save-a-Lot Markets.

  1. Note the customer IDs for each customer, reopen the query in Design view, and enter each ID in the Criteria field under either of the CustomerID fields — either the one from the Orders table or the one from the Customers table — exactly like this: "alfki" or "whitc" or "savea"

Make sure you that you include the quotation marks as shown. You don't need to worry about using uppercase and lowercase letters in your criteria, but Access will capitalize each instance of "or".

 Note   You can put each ID on a separate "Or" line in the Query Designer, but if you do so, you also have to copy the date criteria and paste it to each line. The resulting query would look something like this:

         
Field: OrderID CustomerID CustomerID OrderDate
Table: Orders Orders Customers Orders
Sort:        
Show:        
Criteria:     ALFKI Like "10/*/1997"
Or:     WHITC Like "10/*/1997"
      SAVEA Like "10/*/1997"
  1. Run the query again.

The query returns 10 records. Now you need to change the ship dates.

  1. Reopen the query in Design view and then, on the Query menu, click Update.
  2. In the Update To field under ShippedDate, type the following expression exactly as shown: [ShippedDate]-1
  3. Run the query.

Top of Page Top of Page

 
 
Applies to:
Access 2003