Changing Data with Action Queries

Up to this point in the workshop, we have built Select queries, Totals, and Crosstab queries, which create dynasets that show the most current information in the selected fields and tables. The results of a query may be different at any given time, depending upon the underlying data.

Another type of query is the Action query, which makes changes to the underlying data. Instead of just viewing data, Action queries can easily alter data by updating field values, deleting records, or adding new records. It is easy to see that Action queries can be very powerful tools in performing global data management operations on one or more tables at once. At the same time, they can also be dangerous since an erroneously written query can make incorrect changes to thousands of records. We will keep this in mind and learn how to protect our data as we explore Action queries.

Understanding Action Queries

Select queries will serve users' needs most of the time. That's why they are the default query type. However, sometimes Action queries are needed.

The following table lists the different types of Action queries in Access:

In today's exercises, we will create these Action queries:

• We will use Make Table queries to move building information into its own table.
• We will use a Delete query to remove some extraneous rows from some data we import into our database.
• We will use an Append query to add a set of records from an Excel spreadsheet to an existing table.
• We will use the Update query to modify telephone number prefixes in a large number of records.

Backing up Tables

Because Action queries modify data, they are very powerful. However, if the specifications or expressions used to design an Action query are incorrect, we are left with bad data after the Action query is run. Therefore, we should always make copies or backups of the tables before running an Action query on them.

In our Action query exercises today, we will make changes to tblLocations, tblCourses, and tblFaculty. Let's make backups of these tables before we start building Action queries.

Step1. To select the tables,

Click tblCourses, press and hold: Control key, Click tblFaculty, tblLocations

Step2. To make a copy of the tables, press:

Control key + C

Now that we have made a copy of the tables, we need to paste copies of them into our Navigation pane.

Step3. To paste the copy the tables, press:

Control key + V

The Paste Table As dialog box appears:

The dialog box is asking for a table name. It also gives options for pasting only the structure (i.e., creating a new table with the same structure but no data), the structure and data, or appending the data into an existing table (i.e., allowing the addition of the copied data into another table).

Let's use the default setting, Structure and Data, to make an exact copy of the table. We'll add a "zz" prefix so that all the backup tables will be listed together at the bottom of the tables group in the Navigation pane.

Step4. To name and create the new table for courses, type:

zzBackup_tblCoursesToday'sDate Enter key

Since we copied and pasted all three tables at once, we are now prompted to name the Faculty table. Access will prompt for names in the order you selected the tables if multiple tables are selected when copying and pasting.

Step5. To name the table, type:

zzBackup_tblFacultyToday'sDate Enter key

We are now prompted to name the Locations table.

Step6. To name the table, type:

zzBackup_tblLocationsToday'sDate Enter key

All three tables are backed up in case we don't get the expected results, and in case we need to access the original data.

Verifying Table Backups

Let's open the tables and verify that the copy happened correctly.

Step1. To open the tables,

Double-Click tblCourses, tblFaculty, tblLocations, zzBackup_tblCoursesToday'sDate, zzBackup_tblFacultyToday'sDate, zzBackup_tblLocationsToday'sDate

Our tables and their backups are now open. Let's switch between them and verify that they have the same number of records.

Step2. To view tblCourses,

Click the tblCourses tab

Notice that tblCourses contains 99 records. Let's check the backup.

Step3. To view the backup of tblCourses,

Click the zzBackup_tblCoursesToday'sDate tab

The backup also has 99 records.

Step4. To verify that both tblFaculty and its backup have 124 records,

repeat steps 2 and 3 with tblFaculty and its backup

Step5. To verify that both tblLocations and its backup have 124 records,

repeat steps 2 and 3 with tblLocations and its backup

Step6. To close all tables, at the top of the object workspace,

Right-Click any tab, Click Close All

All tables are closed.