Special Offers

Microsoft® Access Version 2002 Step by Step
Author Online Training Solutions, Inc.
Pages 352
Disk 1 Companion CD(s)
Level Beg/Int
Published 06/06/2001
ISBN 9780735612990
Price $29.99
To see this book's discounted price, select a reseller below.

More Information

About the Book
Table of Contents
Sample Chapter
Related Series
Related Books
About the Author

Support: Book & CD

Rate this book
Barnes Noble Amazon Quantum Books


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:

  1. Open the GardenCo database located in the working folder.
  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 to the Selected Fields list.
  5. Select Table: Products in the Tables/Queries list.
  6. Double-click ProductName and UnitPrice to move them to the Selected Fields list.
  7. Click Finish to accept all defaults and create the query.
  8. 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

  9. Click View to display the query in Design view, where it looks like this:
  10. 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.

  11. In the Criteria row under CategoryName, type bulbs, and then type cacti in the or row.
  12. Click the Run button to run the query and confirm that only bulbs and cacti are listed, and then return to Design view.
  13. 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.

  14. Click the Query menu to display the commands that apply to a query.
  15. 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
    Click to view graphic

    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.

  16. Click Update Query.
  17. 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.

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

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

    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.

  20. Click the View button.
  21. 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.

  22. Return to Design view, and then click the Run button.
  23. Access displays this rather firm warning:

    Click to view graphic
    Click to view graphic

  24. 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.
  25. Save and close the query, and then close the database.

Previous   |  Table of Contents   |   Next

Top of Page

Last Updated: Saturday, July 7, 2001