Creating an Update Query

The one other important type of Action query is the Update query. It can be used to change data in multiple records in a table at once. This is easier than changing the records one by one.

The Faculty table holds work phone numbers for each faculty member. All of these employees currently have the area code of 812. We have just received word that some of the employee phone numbers are being assigned new area codes. Each number with a 776 exchange, as in (812) 776-2488, will switch from the 812 area code to a 444 area code.

Changing each individual phone number manually would be very time consuming. We can design an Update query to indicate which numbers need to change and modify just the area code portion of the affected numbers in one action.

Step1. To create a new query, on the Ribbon,

Click the Create tab, Click 

We will add the Faculty table to the query design grid.

Step2. To add the table, in the Add Tables pane,

Double-Click tblFaculty

The Faculty table is added to the query design grid. The Phone field is the only one we need to update, so we will add it to the form.

Step3. To add the Phone field to the query design window, in the field list,

Double-Click Phone

Next we need to change the query type.

Step4. To select the Update Query type, on the Ribbon, in the Query Type group,

Click 

A new Update To row is added to the query design grid. We will use this field to specify the change to the phone numbers. But first, we will use the Criteria field to specify the records to be changed. We only want to change numbers that have 776 as the set of numbers following the area code.

Step5. To enter an expression to describe the numbers we will change, in the Criteria field, type:

Like "(812) 776*" Enter key

NOTE: Make sure to type a space between the (812) and the 776.

This expression specifies the criteria for the phone numbers that we are looking for. The asterisk is the wildcard we have used before and indicates that anything else could follow these first eight characters

Now that we have the code to find the phone numbers with the proper area code and exchange, we can tell Access how to update the phone numbers. Since the expression we need is complex, let's use the Zoom tool to create it.

Step6. To access the Zoom tool for the Update To row, in the Phone column,

Right-Click the Update To field, Click Zoom...

The Zoom window opens.

Step7. To enter an expression to describe how the phone numbers will change, type:

"(444)"+Right([Phone],9)

The Right function is a built-in function that selects a given number of characters from the right side of a field. In plain English, this expression says, update the phone number to (444) plus the existing 9 characters from the right side of the Phone field. It's 9 characters because there is a space before the local phone number and the local phone number has seven digits plus a hyphen between the third and fourth digits.

Step8. To close the Zoom tool,

Click 

Step10. To preview the query result,

Click 

There are 28 records selected. Unfortunately, the datasheet only shows the list of phone numbers matching the criteria we specified. It does not preview the numbers as they will be modified when we run the update query. Since changing data without testing could be dangerous, we will use a different method to test the Update To expression.

Testing the Update Query

To test the accuracy of the Update expression and its effect on the existing records, we will copy and paste the expression describing the changed numbers into a new field, change the query type back to Select query, and view the values displayed by the expression. We will see the result of the Update expression in the same way we saw the Weighted calculated field results earlier in this workshop.

Step1. To switch to Design View,

Click 

Step2. To expand the first column,

Double-Click between the Phone column and the empty column after it

Step3. To select the Update To expression,

Press & Drag across "(444)"+Right([Phone],9)

Step4. To copy the expression, press:

Control key + C

Continuing this test of the expression, we will switch the query type from the current Update type to the Select query type.

Step5. To switch from an Update query to a Select query, in the Query Type group of the Ribbon,

Click 

The query design grid changes back to the Select query design grid without an Update To row. To test the update, we will use two fields: the Phone field and a calculated field that will use the update expression.

Step6. To create the second field, in the column to the right of the Phone field,

Click the top row, press: Control key + V, Enter key

Step 7. If necessary, expand the second column in order to see the full contents of the Field row.

The grid should now look like this:

Step 8. To view the query result,

Click

This query result shows the phone numbers with 776 as their exchange and it shows, in the second column, those numbers with the new area code.

Step9. Verify that the 776 exchange phone numbers are updated to the 444 area code in the second column of the query results.

Running an Update Query

Now that we have confirmed the update will work as expected, we will switch the query type back to Update, and run the query.

Step1. To switch to Design View,

Click 

Step2. To change the query type back to Update, on the Ribbon,

Click 

The rows of the Update query return with our update expression in place. The second column we created was only needed to test the expression. While we no longer need that second field, Access will ignore it when we run the query so we will just leave it in place.

Step3. To run the query, on the Ribbon,

Click 

We see an Access alert message that we are about to update 28 rows:

Step4. To continue with the update operation,

Click 

To see the result of this query, we must look at the information in tblFaculty. Let's close the query and check the updated phone numbers in the table.

Step5. To close the query without saving changes, at the top of the object workspace,

Right-Click the query tab, Click Close, Click 

Viewing the Update

Let's check the results of the update in tblFaculty.

Step1. To see the effect of the Update query on faculty phone numbers, in the tables list,

Double-Click tblFaculty

We could see the modified phone numbers more clearly by sorting these records by the Phone field.

Step2. To place the cursor, in the Phone column,

Click in any cell

Step3. To sort the records by phone number, on the Ribbon, in the Sort & Filter group,

Click 

We see that all faculty with local the phone number exchange of 776 now have an area code of 444.

It is easy to see that Update queries can change large amounts of data in one quick step.

Step4. Close tblFaculty without saving.

We have seen that Action queries are suitably named. They result in changes to the database that can be helpful and time saving. Remember that it is important to back up the original data before running Action queries to protect yourself against mistakes.