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 misspelled word and replace it with the correct spelling. This command works much like the same commands in Microsoft Office Word or Microsoft Office Excel.

However, if you decide to increase the price of some products or replace the content of a field only under certain circumstances, 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 open the GardenCo database and use an update queryto increase the price of all bulbs and cacti by 10 percent.

USE the GardenCo database in the practice file folder for this topic. This practice file is located in the  My Documents\Microsoft Press\Office 2003 SBS\Accurate\QueryUp folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .

OPEN the GardenCo database and acknowledge the safety warning, if necessary.

1. On the Objects bar , click Queries .

2. In the Queries pane, double-click Create query by using wizard .

3. In the Tables/Queries list, select Table: Categories .

4. Double-click CategoryName to move it from the Available Fields list to the Selected Fields list.

5. In the Tables/Queries list, select Table: Products .

6. Double-click ProductName and UnitPrice to move them from the Available Fields list to the Selected Fields list.

7. Click Finish to accept all defaults and create the query.

   Access displays the query results in a datasheet. Only the Category Name, Product Name , and Unit Price fields are displayed.

   

8. Click the View button to display the query in Design view.

   

   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.

9. In the Criteria row under CategoryName , type bulbs , and then type cacti in the or row.

10. Click the Run button to run the query and confirm that only bulbs and cacti are listed, and then return to Design view.

    A select query that selects just the records you want to change is created. But to actually make a change to the records, you have to use an update query.

11. Click the Query menu to display the commands that apply to a query.

    The four available action queries are listed toward the middle of the menu, with exclamation points in their icons.

    

    Tip  

    You can t create an action query directly; you must 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.

12. On the Query menu, click Update Query .

    The select query is converted to an update query. The only noticeable changesto the design grid are that the Sort and Show rows have been removed and an Update To row has been added.

13. In the Update To row under UnitPrice , type [UnitPrice]*1.1 .

    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 tablein 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. Because of this, you should create a backup copy of the table before running the query. For the purposes of this exercise, however, 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 [CONTROL]+[C] followed by [CONTROL]+[V]. In the dialog box that appears, provide a name for the backup table, and click OK.

14. Click the View button.

    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.

15. Return to Design view, and then click the Run button.

    Access displays a rather firm warning.

    

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

17. Save and close the query.

CLOSE the GardenCo database.