Part 7 of "Keeping Your Information Accurate" in Access 2003 and Access 2002: "Updating Information in a Table" (311174)

MORE INFORMATION

As you use a database and as it grows, you might discover that errors creep in or that some information becomes out of date. You can tediously scroll through the records looking for those that need to be changed. But it is more efficient to use a few of the tools and techniques provided by Access for that purpose.

If an employee has consistently misspelled the same word, you can use the Find and Replace commands on the Edit menu to locate each instance of the misspelling and replace it with the correct spelling. This command works much like the same commands in Microsoft Word or Microsoft Excel.

However, if you decide to increase the price of some products or replace the content of a field only under certain circumstances, the Find and Replace commands won't be much use. For this task, you need the power of an update query, which is a select query that performs an action on the query's results. To create a query

1. Open the database you want to use.
2. In the Database window, click Queries under Objects.
3. Click Create query by using wizard.
4. In the Tables/Queries list, select the table or query you want to use.
5. In the Available Fields list, double-click an item to move it to the Selected Fields list. Repeat this step for each item you want to move to the Selected Fields list.
6. Click Finish to accept all defaults and create the query.
   
   Access displays the query results in a datasheet. Only the items that you moved to the Selected Fields list are displayed, similar to this:
   
   

To update a query

1. Click View to display the query in Design view, where it looks similar to this:
   
   
2. Click the Query menu to display the commands that apply to a query.
   
   The four action queries are listed toward the middle of the menu with exclamation points in their icons, as shown here:
   
   
   
   TIP: You can't create an action query directly; you first create a select query and then change the query to one of the action types. With an existing select query open, you can find the command to convert it to an action query either on the Query menu, in the list that appears when you click the Query Type button's arrow, or on the shortcut menu that appears when you right-click the query and point to Query Type.
3. Click Update Query.
   
   The select query is converted to an update query. The only noticeable changes to the design grid are that the Sort and Show rows have been removed and an Update To row has been added.
4. In the Update To cell for the fields you want to update, type the expression or value that you want to use to change the fields.
   
   TIP: You can quickly create a backup copy of a table by displaying the Tables pane in the database window, clicking the table you want to back up, and then pressing CTRL+C followed by CTRL+V. In the dialog box that appears, provide a name for the backup table, and click OK.
5. To see a list of the records that will be updated, click View on the toolbar. This list won't show the new values. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.
   
   In a select query, clicking the View button is the same as clicking the Run button. But in an update query, clicking the View button simply displays a list of the fields that will be updated.
6. Click Run on the toolbar to update the records.
   
   Access displays this rather firm warning:
   
   
7. Click Yes to acknowledge the warning, and then click the View button again to see the updated data.
8. Save and close the query, and then close the database.

ADDITIONAL RESOURCES