WORKING WITH ACTION QUERIES: FOUNDATIONS FOR POWER

Action queries can manipulate large numbers of records very quickly. They also provide the basis for moving data into a database and managing data once it resides in tables. The best way to develop an Action query is to develop a standard Select query, and then convert it to an Action query. This way, you can view the records that will be used or affected by the query and then determine whether the query criteria is correct before creating the Action query.

You can create Action queries from the query design grid from the query menu by choosing one of the Action query types. Several types of Action queries are available to insert data and manipulate existing data in the database. Table 8.2 shows the types of queries and the Access SQL keywords used with the queries.

To become familiar with Action queries, let's start with a Select query in the query design grid and explore each Action query's capabilities. Each query in Table 8.2 is discussed in more detail in the following sections, which include example SQL code.

Make Table Query (SELECT INTO)

Access allows you to create queries that can create new tables as part of query execution. This is a great way to create tables quickly and easily, but it doesn't allow for the creation of indexes. On calculated fields, Access will guess at the new data type for a created field, which might not match the type of data you were expecting. The following is an example of the SQL code for a Make Table query:

SELECT DISTINCTROW Customers.LastName, Customers.FirstName
INTO tblNewCustomer FROM Customers;

Although you can use the Make Table query to create new tables, consider using it only when you create ad hoc tables or when you need to allow for new fields to be automatically created in the output table. To allow for new fields, use the asterisk (*) in the field name. The Make Table query then automatically creates the new table, including any new fields that are added to the original base table.

When you use Data Definition Language (DDL) queries, you can define the data types as part of the CREATE TABLE statement. After the table is created, you can add the records by using an Append query (discussed in the next section). Unless you need the flexibility of making the new table based on changes in the definition of a base table, you should create the table with the DDL query CREATE TABLE statement. DDL queries are covered later in the section “Queries that Create Objects (DDL).”

Append Query (INSERT INTO)

Append queries add records to an existing table from one or more tables or queries. You can also use Append queries to write a single record to a table that's based on information on a form or based on constants entered directly into the query design grid. To create an Append query, follow these steps:

If the goal is to create new records that have all fields in the appended table filled in, go to the Append To line. On each column of the query design grid, select a field from the table to be appended to. Now return to the first column and begin filling in the Field line with the formulas or field references that will produce the correct values for the field. Here's an example of the SQL code for an Append query:

INSERT INTO tblNewCustomer ( LastName, FirstName )
SELECT DISTINCTROW Customers.LastName, Customers.FirstName
FROM Customers;

You can also use Append queries to quickly clone information from existing records into other records in the same table. For example, if a series of records contains most of the information you need to create a new set of records, you can create the new records by building an Append query. Build a Select query with the information to transfer, and then choose Append from the Query menu. The table to append to is the same as the table used in the Select query, so choose the table in the Append dialog and click OK. If the fields are copied directly from the query source table to the append table, all the field names will automatically be added to the grid's Append To line.

Update Query (UPDATE..SET)

Update queries update existing records with calculated data, constants, or data from other tables. Update queries are useful for setting status codes or calculating values of fields based on other fields in the table. In the following Update query, all the records in the Customer table will have the City field set to Woodinville:

UPDATE DISTINCTROW Customers SET Customers.City = "Woodinville";

Delete Query (DELETE)

Use Delete queries to remove records from a table. This can be quite dangerous, however, so make sure that the criteria are correct before executing a Delete query. Later, the section “Finding and Deleting Duplicate Records” shows an example of how to delete duplicate records in a table by using a Delete query. The following Delete query deletes all the records in the Customers table:

DELETE Customers.* FROM Customers;