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:
|Action Query Type||Description|
|Make Table||Creates a new table which contains selected data|
|Update||Updates table data according to query specifications|
|Append||Appends data from one table to another according to query specifications|
|Delete||Deletes records according to query specifications|
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,
press: Control key and Click tblFaculty,
press: Control key and Click tblLocations
Step2. To make a copy of the tables, press:
Step3. To paste the copy the tables, press:
Step4. To name and create the new table for courses, type:
Step5. To name the table, type:
Step6. To name the table, type:
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
Step2. To view tblCourses,
Click the tblCourses tab
Step3. To view the backup of tblCourses,
Click the zzBackup_tblCoursesToday'sDate tab
Step4. To verify that both tblFaculty and its backup have 124 records,
repeat steps 2 and 3
Step5. To verify that both tblLocations and its backup have 124 records,
repeat steps 2 and 3
Step6. To close all tables,
Right-Click any tab, Click Close All