MS Access —

Update Queries

You can use an update query to change a pile of records at the same time. For example, you could create an update query to lower prices by eight percent or to change the sales representative for all your clients in Oregon from "Mr. Potter" to "George Bailey."

Just like other action queries, you create an update query by first creating a select query and then converting the select query to an update query.

An query.

Access asks you to confirm the record update.

The update query

In this lesson you will create an update query to raise the prices of all trips to Europe by 10 percent.

  1. From the Database window, click the Queries icon in the Objects bar and click the New button.

    The New Query dialog box appears.

  2. Select Design View and click OK.

    The query design window and Show Table dialog box both appear. Here you have to select the tables and/or queries you want to use in the update query.

  3. Double-click the tblTours table and click Close.

    Now you need to convert the select query to an update query. Here's how:

  4. Click the Query Type button list arrow and select Update Query from the list.

    Access converts the select query to an update query. Notice an Update To row appears in the design grid. Now you have to specify the fields you want to update.

  5. Double-click the TourID, Normal Price, and First Class Price fields in the tblTours field list.

    The next step is a little bit trickyyou have to tell Access which fields to update and how to update them. You want to raise the price of both the Normal Price and First Class Price fields by 10 percentyou will have to write an expression (or formula) in the Update To rows of both fields to make this little bit of magic happen.

  6. Click the Normal Price column's Update To row and type [Normal Price]+([Normal Price]*.1).

    So what does this confusing expression actually mean? Let's assume the Normal Price for a record is $100. The expression would then look something like this: [$100] + ([$100] * .1).

    Access first calculates anything it sees in parentheses, so it multiples $100 x 0.1 (or 10%) and comes up with $10. Access then adds this $10 to the Normal Price (as in $10 + $100) and comes up with $110. Make a little more sense? Hope sobecause you have to do the same thing to the First Class Price field.

  7. Click the First Class Price column's Update To row and type:

    [First Class Price]+([First Class Price]*.1).

    Next you need to specify any limiting criteria. You want to raise prices for only those tours that are in Europe. Sadly, the only way to determine this is by looking at the values in the tblTours table. Here are all the European TourIDs: 1, 2, 3, and 9. You will have to create an OR statement in your query to make sure you get them all.

  8. Click the TourID field's Criteria row and type 1.

    This will select the record whose TourID is "1," but what about the rest?

  9. Press the (down arrow key) to select the TourID field's second Criteria row (the or row) and type 2.

    This will select the records whose TourID is "2." Go to the next step and finish adding the rest of the OR criteria.

  10. Press the (down arrow key) to select the TourID field's third Criteria row, type 3, press the (down arrow key) to select the TourID field's fourth Criteria row and type 9.

    Compare your query to the one in figure. Look the same? Let's run the query.

  11. Click the Run button on the toolbar.

    Access asks about the updating the selected records, as shown in figure.

  12. Click Yes.

    Access updates the selected records by raising both their Normal Price and First Class Price fields by 10 percent. Let's open the new table.

  13. Close the query without saving your changes, click the Tables icon in the Objects bar, and double-click the tblTours table.

    Compare your table with figure. Notice that the prices of four European tours have been increased by ten percent.

  14. Close the tblTours table and close the database.

To create an update query:

  1. create a new query in design view, then select the tables and/or queries you want to use in the update query.

  2. click the query type button list arrow on the toolbar and select update query.

    or...

    select query » update query from the menu.

  3. double-click the fields that you want to appear in the query or click and drag the fields onto the design grid.

  4. enter an expression to update the selected field.

  5. enter any criteria, if needed, to select which records should be updated.

  6. click the view button to view the results of the update query.

  7. if you're satisfied that the appropriate records will be updated, click the run button on the toolbar and click yes to confirm the update.