Chapter 18: Advanced Access Query Techniques
675
When working with one-to-many relationships without defining relationships and turning Cascade
Delete on, Access deletes records from one table at a time. Specifically, Access first deletes the
many side (that is, the child records) of the relationship. Then you must remove the many table
from the query and delete the records from the one side of the query.This method is time-consuming and awkward. So, when you’re deleting related records from one-
to-many relationship tables, make sure that you define relationships between the tables and check
the Cascade Delete box in the Edit Relationships dialog box. By doing this, you can delete from all
related tables by creating a single Delete query.No extensive description of a delete query is really necessary because the pattern is exactly the
same as the other action queries. Start with a select query that retrieves the records you want to
delete, preview the records in the query’s Datasheet view, and then click the Delete button in the
Query Type ribbon group. Access always confirms deletions when you execute the query by click-
ing on the Run button in the Results ribbon group.Figure 18.36 illustrates a typical delete query. Its similarity to the update and make-table queries
described earlier in this section should be obvious. In fact, the only difference between
qryDeleteQuery and qryMakeTable is the presence of the Delete row, and the absence of the
Sort and Show rows in the QBE grid in qryDeleteQuery.FIGURE 18.36A typical delete query
It deserves repeating that a delete query permanently and irreversibly removes the records from the
database. You may want to use the query’s design as an append query to provide a backup of the
records before changing it to a delete query.Running an action query
After you save an action query, you run it by double-clicking its name in the Navigation Pane.
Access warns you that an action query is about to be executed and asks for confirmation before it
continues with the query.