Changing the Content of Tables Using Action Queries

Changing the Content of Tables Using Action Queries
When we create a query, Microsoft Office Access creates a select query unless we choose
a different type from the Query menu. When we run a select query, Microsoft Office
Access displays the resulting datasheet. As the datasheet is updatable, we can make
changes to the data; however, we must make the changes record by record.
If we require a large number of similar changes, we can save time by using an action
query. An action query allows us to make changes to many records at the same time.
There are four types of action query: make-table, delete, update, and append.

• Make-Table Action Query

The make-table action query creates a new table from all or part of the data in one or
more tables. The newly created table can be saved to the currently opened database
or exported to another database. Note that the data in the new table does not inherit the
field properties including the primary key from the original table, which needs to be set

Figure 7.14 (a) QBE grid of example autolookup query; (b) datasheet based on autolookup query; (c) equivalent SQL statement.

manually. Make-table queries are useful for several reasons including the ability to archive

historic data, create snapshot reports, and to improve the performance of forms and reports

based on multi-table queries.

Suppose we want to create a new table called StaffCut, containing only the staffNo, fName,

lName, position, and salary fields of the original Staff table. We first design a query to target

the required fields of the Staff table. We then change the query type in Design View to

Make-Table and a dialog box is displayed. The dialog box prompts for the name and location

of the new table, as shown in Figure 7.15(a). Figure 7.15(b) displays the QBE grid

for this make-table action query. When we run the query, a warning message asks whether

we want to continue with the make-table operation, as shown in Figure 7.15(c). If we continue,

the new table StaffCut is created, as shown in Figure 7.15(d). Figure 7.15(e) displays

the equivalent SQL statement for this make-table action query.

• Delete Action Query

The delete action query deletes a group of records from one or more tables. We can use

a single delete query to delete records from a single table, from multiple tables in a oneto-

one relationship, or from multiple tables in a one-to-many relationship with referential

integrity set to allow cascading deletes.

For example, suppose that we want to delete all properties for rent in Glasgow and

the associated viewings records. To perform this deletion, we first create a query that

targets the appropriate records in the PropertyForRent table. We then change the query

type in Design View to Delete. The QBE grid for this delete action query is shown in

Figure 7.16(a). As the PropertyForRent and Viewing tables have a one-to-many relationship

with referential integrity set to the Cascade Delete Related Records option, all the associated

viewings records for the properties in Glasgow will also be deleted. When we run

the delete action query, a warning message asks whether or not we want to continue with

the deletion, as shown in Figure 7.16(b). If we continue, the selected records are deleted

from the PropertyForRent table and the related records from the Viewing table, as shown

in Figure 7.16(c). Figure 7.16(d) displays the equivalent SQL statement for this delete

action query.

• Update Action Query

An update action query makes global changes to a group of records in one or more tables.

For example, suppose we want to increase the rent of all properties by 10%. To perform

this update, we first create a query that targets the PropertyForRent table. We then change

the query type in Design View to Update. We enter the expression ‘[Rent]*1.1’ in the

Update To cell for the rent field, as shown in Figure 7.17(a). When we run the query, a

warning message asks whether or not we want to continue with the update, as shown in

Figure 7.17(b). If we continue, the rent field of PropertyForRent table is updated, as shown

in Figure 7.17(c). Figure 7.17(d) displays the equivalent SQL statement for this update

action query.

Figure 7.15 (a) Make-Table dialog box; (b) QBE grid of example make-table query; (c) warning message; (d) resulting datasheet; (e) equivalent SQL statement.

• 7.4 Changing the Content of Tables Using Action Queries

Figure 7.16 (a) QBE grid of example delete action query; (b) warning message; (c) resulting PropertyForRent and Viewing datasheets with records deleted; (d) equivalent SQL statement.

Figure 7.17 (a) QBE grid of example update action query; (b) warning message; (c) resulting datasheet; (d) equivalent SQL statement.

• Append Action Query

We use an append action query to insert records from one or more source tables into a

single target table. We can append records to a table in the same database or in another

database. Append queries are also useful when we want to append fields based on criteria

or even when some of the fields do not exist in the other table. For example, suppose that

we want to insert the details of new owners of property for rent into the PrivateOwner table.

Assume that the details of these new owners are contained in a table called NewOwner with

only the ownerNo, fName, lName, and the address fields. Furthermore, we want to append

only new owners located in Glasgow into the PrivateOwner table. In this example, the

PrivateOwner table is the target table and the NewOwner table is the source table.

To create an append action query, we first design a query that targets the appropriate

records of the NewOwner table. We change the type of query to Append and a dialog box

is displayed, which prompts for the name and location of the target table, as shown in

Figure 7.18(a). The QBE grid for this append action query is shown in Figure 7.18(b).

When we run the query, a warning message asks whether we want to continue with the

append operation, as shown in Figure 7.18(c). If we continue, the two records of owners

located in Glasgow in the NewOwner table are appended to the PrivateOwner table, as given

in Figure 7.18(d). The equivalent SQL statement for the append action query is shown in

Figure 7.18(e).