Creating and Running Action Queries

With Action queries, you can easily modify data without writing any code. In fact, using Action queries is often a more efficient method of modifying data than using code. Four types of Action queries are available: Update, Delete, Append, and Make Table. You use Update queries to modify data in a table, Delete queries to remove records from a table, Append queries to add records to an existing table, and Make Table queries to create an entirely new table. The sections that follow explain these query types and their appropriate uses.

Creating and Running Update Queries

You use Update queries to modify all records or any records that meet specific criteria. You can use an Update query to modify the data in one field or several fields (or even tables) at one time. For example, you could create a query that increases the salary of everyone in California by 10%. As mentioned previously, using Action queries, including Update queries, is usually more efficient than performing the same task with Visual Basic for Applications (VBA) code, so you can consider Update queries a respectable way to modify table data.

To build an Update query, follow these steps:

You should name Access Update queries with the prefix qupd. In fact, you should give each type of Action query a prefix indicating what type of query it is. This makes your application easier to maintain, makes your code more readable, and renders your code self-documenting. Table 15.1 lists all the commonly accepted prefixes for Action queries.

Table 15.1. Naming Prefixes for Action Queries

Type of Query	Prefix	Example
Update	qupd	qupdDefaultRate
Delete	qdel	qdelOldTimeCards
Append	qapp	qappArchiveTimeCards
Make Table	qmak	qmakTempSales

Access stores all queries as Structured Query Language (SQL) statements. You can display the SQL for a query by selecting SQL View from the View drop-down list on the toolbar. The SQL behind an Access Update query looks like this:

UPDATE tblClients SET tblClients._
    DefaultRate = [DefaultRate]*1.1
     WHERE (((tblClients.StateProvince)="CA"));

Creating and Running Delete Queries

Rather than simply modify table data, Delete queries permanently remove from a table any records that meet specific criteria; they're often used to remove old records. You might want to use a Delete query to delete all orders from the previous year, for example.

To build a Delete query, follow these steps:

The SQL behind a Delete query looks like this:

DELETE tblTimeCards.DateEntered
     FROM tblTimeCards
     WHERE (((tblTimeCards.DateEntered)<Date()-365));

Creating and Running Append Queries

You can use Append queries to add records to existing tables. You often perform this function during an archive process. First, you append to the history table the records that need to be archived by using an Append query. Next, you remove the records from the master table by using a Delete query.

To build an Append query, follow these steps:

The SQL behind an Append query looks like this:

INSERT INTO tblTimeCardsArchive ( TimeCardID, EmployeeID, DateEntered )
    SELECT tblTimeCards.TimeCardID, tblTimeCards.EmployeeID,
    tblTimeCards.DateEntered
    FROM tblTimeCards
    WHERE (((tblTimeCards.DateEntered) Between #1/1/95# And #12/31/95#));

Append queries don't allow you to introduce any primary key violations. If you're appending any records that duplicate a primary key value, the message box shown in Figure 15.21 appears. If you go ahead with the append process, Access appends to the destination table only records without primary key violations.

Creating and Running Make Table Queries

Whereas an Append query adds records to an existing table, a Make Table query creates a new table, which is often a temporary table used for intermediary processing. You might want to create a temporary table, for example, to freeze data while you are running a report. By building temporary tables and running a report from those tables, you make sure users can't modify the data underlying the report during the reporting process. Another common use of a Make Table query is to supply a subset of fields or records to another user.

To build a Make Table query, follow these steps:

If you try to run the same Make Table query more than one time, Access permanently deletes the table with the same name as the table you're creating. (See the warning message in Figure 15.25.)

The SQL for a Make Table query looks like this:

SELECT tblTimeCards.TimeCardID, tblTimeCards.EmployeeID,
     tblTimeCards.DateEntered, [DateEntered]+365 AS ArchiveDate
     INTO tblOldTimeCards
     FROM tblTimeCards
     WHERE (((tblTimeCards.TimeCardID) Between 1 And 10));