Chapter 6: Keeping Your Information Accurate continued
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.
In this exercise, you will use an update query to increase the price of all bulbs and cacti by 10 percent. The working folder for this exercise is SBS\Access\Accurate\QueryUp. Follow these steps:
Access displays the query results in a datasheet. Only the Category Name, Product Name, and Unit Price fields are displayed, as shown here:
This query displays the products in all categories. You want to raise the prices of only the bulbs and cacti, so your first task is to change this query so that it selects just those categories.
You have created a select query that selects just the records you want to change. But to actually make a change to the records, you have to use an update query.
The four available action queries are listed toward the middle of the menu, with exclamation points in their icons, as shown here:
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.
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.
You enclose UnitPrice in brackets to indicate that it is an Access object. If you use the Expression Builder to insert this expression, it looks like this: [Products]![UnitPrice]*1.1. Because this description of the field includes the table in which it is found, this expression can be inserted in other tables.
When you run an update query, you make changes to the table that can’t be undone. For a real database, you should create a backup copy of the table before running the query. In this case, before running the query you will perform one simple check.
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.
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. In this case, you see a list of unit prices that matches the ones shown earlier in the select query.
Access displays this rather firm warning:
Last Updated: Saturday, July 7, 2001