In This Chapter

Updating multiple records that match specific criteria

Adding records from one table into another table

Deleting multiple records that match specific criteria

Ever have to update multiple records in a table with the same information? How about copying data from a linked spreadsheet into an existing Access table? What about deleting a certain group of records from a table? If you answered yes to any of these questions, then you’ve come to the right place. Action queries perform a specific task on a group of records in a table, all in one fell swoop, so you don’t have to add or update each record manually. (Now, try to control your excitement! If you ever get into the situation where an action query is needed, I know you’ll thank me for writing this.)

The three most common action queries are

• Update: This query updates the value in a field for the records you select via the Criteria row. For example, if an employee keys in the wrong order date of 11/2/2015 on 25 orders, an update query would allow you to easily update all 25 order dates to the correct date of 11/3/2015.
• Append: Use this query type to add records from one table to another. For example, your credit card company gives you monthly expense transactions in an Excel spreadsheet, and you need to input the expenses into your Access database Expense table. You can link or import (see Chapter 9 for details) the spreadsheet into your Access database and then use an append query to add the records to your Expense table. An append query gives you control over what records you add from the spreadsheet to the Expense table. Importing directly to the Expense table will import every record from the spreadsheet.
• Delete: If you’re in a destructive mood, use this query to delete groups of records from a table. Suppose you work for a company that has discontinued a product as of 12/1/15 — and customers have been notified that any order dated after 12/1 for delivery of the discontinued product will be deleted. Okay, they were warned; you can use a Delete query to eliminate all orders for the discontinued product that have delivery dates after 12/1/15.

The action queries described in this chapter will alter data in your database PERMANENTLY. Although they’re beneficial when used correctly, they can wreak havoc if used incorrectly. The results of running an action query cannot be undone. Therefore, it’s wise to back up your database file before you run any action query!

Easy Update

The Update query can replace the value in a field in a group of records with another value. To create an Update query, first you need to determine the table (and the field within it) in need of updating — and decide how you want to update that field. When you’ve figured that out, do this:

1. Click the Create tab on the Ribbon.

   The Create buttons appear onscreen. Notice the Queries group toward the left side of the Ribbon.

2. Click the Query Design button from the Queries group on the Ribbon (see Figure 17-1).

   A new query opens in Design view, and the Show Table dialog box pops up.

3. Select the table that contains the field you’d like to update; then click the Add button.

   The selected table is added to the top half of the Query Design window, as Figure 17-2 illustrates.

4. Click the Close button in the Show Table window.

   The Show Table window closes.

5. From the Table field list, double-click the field name that the query will update along with any fields you’ll need to write criteria.

   The fields are added to the bottom half of the Query Design window (see Figure 17-3).

6. Click the Design tab on the Ribbon.

   The Ribbon displays the design commands, including the Query Type group.

7. Select the Update button from the Query Type group.

   The Update To row appears on the query grid, as Figure 17-4 illustrates.

8. In the Update To row of the field you want updated, enter a value or expression to update the field.

   The Update To row can contain a static value such as a status (Active), an expression such as Date()+10, or the name of a field such as [RequiredDate].

   If you’re not sure what you can enter in the Update To row, make sure the cursor is in the row and click the Builder button from the Design tab of the Ribbon. Expression Builder shows you your options and helps with syntax.

9. In the Criteria row, enter the criteria that will select the records you’d like updated.

   The query in Figure 17-5 updates the status in the Status field to Active for those volunteers in Pennsylvania with a current status of Prospective.

10. Click the Run button from the Ribbon’s Results group to run the query and update the records.

    A message box (see Figure 17-6) appears, telling you how many records will be updated.

    If you’re not sure what records you’ll update, change the query type to a Select query and run it. If the resulting records are correct, switch back to an Update query and you’ll run the update with confidence knowing what records you’re updating. Switch query type using the Query Type group on the Ribbon’s Design tab.

11. Click Yes in the message box window to run the query and update your data.

    Figure 17-7 shows the state of things before you run an update, and Figure 17-8 shows what changes an update has produced.

Update queries will update the data you specify — but the update can’t be undone. Back up your data before you run Update queries — and use them with caution! Don’t say I didn’t warn you!

Add Records in a Flash

Append queries add records from one table (called the source table) to another table (called the destination). A common use for an Append query is to add data from an external file (such as an imported or linked spreadsheet) to an existing Access table. To create an Append query, follow these steps:

1. Click the Create tab on the Ribbon.

   The Create buttons appear onscreen. Notice the Queries group toward the left side of the Ribbon.

2. Click the Query Design button from the Queries group (as shown earlier in Figure 17-1).

   A new query opens in Design view, and the Show Table dialog box pops up.

3. Select the table that contains the source data to be appended, and then click the Add button.

   The selected table is added to the top half of the Query Design window.

4. Click the Close button in the Show Table window.

   The Show Table window closes.

5. From the Table field list, double-click the field names that contain the data to be added to the destination table.

   The fields are added to the bottom half of the Query Design window (see Figure 17-9).

6. Click the Design tab on the Ribbon.

   The Ribbon displays the Design buttons, including the buttons in the Query Type group.

7. Select the Append button from the Query Type group (see Figure 17-11).

   The Append dialog box appears, as Figure 17-10 illustrates.

8. Select the destination table from the Table Name drop-down list, and then click OK to close the Append dialog box.

   The Append To row appears in the query just above the Criteria row. If a source field name matches exactly a destination field name, the Append To row will pre-populate with each matching field name from the destination table.

   If the source and destination field names do not match for a specific field, the Append To row will not pre-populate with a field name from the destination table. To fix this, click in the Append To row with a missing destination field name. A drop-down arrow appears to the right. Select the matching destination field from the drop-down list.

9. Add criteria to the Criteria row in Query Design if necessary, to select just those records you want from the source table.

   See Figure 17-11 for the completed Append query.

   In most cases, the source and destination fields must be of the same data type. For example, you cannot append data from a text field to a number field.

10. Click the Run button from the Ribbon’s Results group to run the query and append the records from the source table to the destination table.

    A message box appears, telling you how many records will be added.

    When Append queries add records to the destination table, you cannot undo the results. Be sure to back up your database file before you run an Append query.

11. Click Yes in the message-box window to run the query and add the records.

    If you run an Append query in error, you can take one of two actions:

    • Delete the appended records from the destination table manually (they will be near the bottom) or via a Delete query (see next section).
    • Revert to your backup file, correct the problem, and try again.

Quick Cleanup

The Delete query can clean up unwanted records in a hurry. Of the three action queries that have been discussed in this chapter, the Delete query is the most dangerous: It can wipe out all the data in your table in an instant. Pay special attention to the selection criteria you write for a Delete query so you’re sure you’ll delete only the correct records. To build a Delete query, follow these steps:

1. Click the Create tab on the Ribbon.

   The Create buttons appear onscreen. Notice the Queries group toward the left side of the Ribbon.

2. Click the Query Design button from the Queries group (as shown previously in Figure 17-1).

   A new query opens in Design view and the Show Table dialog box pops up.

3. Select the table that contains the data to be deleted, and then click the Add button.

   The selected table is added to the top half of the Query Design window.

4. Click the Close button in the Show Table window.

   The Show Table window closes.

5. From the Table field list, double-click the asterisk (*) at the top of the list and any individual field names you intend to use for criteria.

   The fields will be added to the bottom half of the Query Design window. The word From is added to the Delete row in the Query Design under the asterisk to indicate you’re deleting all records that match criteria written in the Criteria row from the selected table.

6. Click the Design tab on the Ribbon.

   The Ribbon displays the Design buttons, including the Query Type group.

7. Select the Delete button from the Query Type group.

   The Delete row appears in the query grid, as pictured in Figure 17-12.

8. Add criteria to the Criteria row in Query Design if necessary, to select just those records you want to delete from the table.

   See Figure 17-13 for the completed Delete query.

   If you don’t write any criteria, every record in the selected table will be deleted!

   After you’ve written your criteria, change the query back to a Select query using the Query Type group on the Ribbon’s Design tab, to see which records will be deleted. If the Select query returns the records you wanted to delete, change the query back to a Delete query, run it, and you’ll know you deleted the correct records. What a relief!

9. Click the Run button from the Ribbon’s Results group to run the query and delete the records from the selected table.

   A message box will appear, telling you how many records will be deleted.

   A Delete query deletes records permanently from the selected table. You cannot undo the result of running this query type. Back up your database file before running a Delete query!

10. Click Yes in the message box window to run the query and delete the records.