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 you want to find or replace multiple instances of the same word or phrase, you can use the Find and Replace commands on the Edit menu. This command works much like the same commands in Microsoft Office Word or Microsoft Office Excel.

If you want to change information stored in the database 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.

Tip 

Running an update query makes irreversible changes to the table; therefore, you should always create a backup copy of the table before running a query.

You can quickly create a copy of a table by displaying the Tables list in the Navigation Pane, clicking the table you want to copy pressing Ctrl+C, and then pressing Ctrl+V to paste a copy. In the Paste Table As dialog box, type a name for the new table, and then click OK.

In this exercise, you will create an update query to increase the price of selected items by 10 percent.

Use the 07_Update database. This practice file is located in the Chapter12 subfolder under SBS_Office2007.

Open the 07_Update database.

1. On the Create tab, in the Other group, click the Query Wizard button.

2. In the New Query dialog box, with Simple Query Wizard selected, click OK.

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

4. In the Available Fields list, double-click CategoryName to move it to the Selected Fields list.

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

6. In the Available Fields list, double-click ProductName and UnitPrice to move them to the Selected Fields list.

7. In the Simple Query Wizard dialog box, click Finish to create the query using the default detail setting and title.

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

8. Display the query in Design view.

The current query results include the products in all categories. You want to raise the prices of only the products in the bulbs and cacti categories, so your next task is to change the query to select only those categories.

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

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

The query now selects only the records you want to change. But to actually make a change to the records, you have to use an update query.

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 in Design view, you can find the command to convert it to an action query in the Query Type group and on the shortcut menu that appears when you right-click the query window and then point to Query Type.

11. Display the query in Design view. On the Design contextual tab, in the Query Type group, click the Update button.

Access converts the select query to an update query. In the design grid, the Sort and Show rows disappear and an Update To row appears.

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

Tip 

Enclosing UnitPrice in brackets indicates 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, you can also insert this expression in other tables.

13. Display the query in Datasheet 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. In this case, you see a list of unit prices that matches the ones shown earlier in the select query.

14. Switch to Design view. Then on the Query Tools Design contextual tab, in the Results group, click the Run button.

Access displays a warning that you can’t undo the changes you are about to make, and asks you to confirm that you want to update the records.

15. In the Microsoft Office Access message box, click Yes. Then click the View Datasheet button to display the UnitPrice field, where all the prices have been increased by 10 percent.

16. Save and close the query.

Close the 07_Update database.