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
Step2. To add the table,
Step3. Close the Show Table dialog box.
Step4. To add the Phone field to the query design window, in the field list,
Step5. To select the Update Query type, on the Ribbon, in the Query Type group,
Step6. To enter an expression to describe the numbers we will change, in the Criteria field, type:
Like "(812) 776*" Enter
NOTE: Make sure to type a space between the (812) and the 776.
Step7. To access the Zoom tool for the Update To row, in the Phone column,
Right-Click the Update To field, Click Zoom...
Step8. To enter an expression to describe how the phone numbers will change, type:
Step9. To close the Zoom tool,
Step10. To preview the query result,
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,
Step2. To expand the Expr1 column,
Double-Click between the Phone column and the empty column after it
Step3. To select the Update expression,
Press & Drag across "(444)"+Right([Phone],9)
Step4. To copy the expression, press:
Step5. To switch from an Update query to a Select query, in the Query Type group of the Ribbon,
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
7. To view the query result,
Step8. Verify that yours is correct.
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,
Step2. To change the query type back to Update, on the Ribbon,
Step3. To run the query, on the Ribbon,
Step4. To continue with the update operation,
Step5. To close the query without saving changes,
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,
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,
Step4. Close tblFaculty without saving.