|
|
|
|
|
|
|
|
|
|
|
| Microsoft® Access Version 2002 Step by Step |
|
|
|
|||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||
Chapter 6: Keeping Your Information Accurate continued
Updating Information in a Table
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.
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:
- Open the GardenCo database located in the working folder.
- In the Queries pane, double-click Create query by using wizard.
- In the Tables/Queries list, select Table: Categories.
- Double-click CategoryName to move it to the Selected Fields list.
- Select Table: Products in the Tables/Queries list.
- Double-click ProductName and UnitPrice to move them to the Selected Fields list.
- Click Finish to accept all defaults and create the query.
- Click View to display the query in Design view, where it looks like this:
- In the Criteria row under CategoryName, type bulbs, and then type cacti in the or row.
- Click the Run button to run the query and confirm that only bulbs and cacti are listed, and then return to Design view.
- Click the Query menu to display the commands that apply to a query.
- Click Update Query.
- In the Update To row under UnitPrice, type [UnitPrice]*1.1
- Click the View button.
- Return to Design view, and then click the Run button.
- Click Yes to acknowledge the warning, and then click the View button again to display the UnitPrice field, where all the prices have been increased by 10 percent.
- Save and close the query, and then close the database.
Access displays the query results in a datasheet. Only the Category Name, Product Name, and Unit Price fields are displayed, as shown here:
Click to view graphic
Click to view graphic
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:
Click to view graphic
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.
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.
TIP:
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.
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.
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:
Click to view graphic
| Top of Page |
Last Updated: Saturday, July 7, 2001