Microsoft Office Tutorials and References
In Depth Information
Deleting Lots of Records with Delete Queries
Before you run a delete query, you need to be aware of how the table you’re
deleting data from is related to other tables in the database. In some cases,
running a delete query can delete records in related tables. If the table
you’re deleting data from is on the “one” side of a one-to-many relationship,
and cascading deletes are enabled for the relationship, Access looks for
related data to delete. The Products table, which holds information for all
the sold products, is related to the Order Details table, where ordered items
are listed. The relationship is one-to-many, with Products on the “one” side.
When you created the relationship between the two tables by using the Edit
Relationships dialog box (displayed in the Relationships window), if you
selected Enforce Referential Integrity and Cascade Delete Related Records,
deleting records from the Products table results in deleting records from the
Order Details table. Customers may not get the products they ordered, and
no record of their orders for those items will exist in the database. In this
case, adding a Discontinued field to the Products table may be a better
solution than deleting the records! (For more information on one-to-many
relationships, see Book I, Chapter 3. For more information on referential
integrity, see Book II, Chapter 6.)
When you tell Access to create a delete query, the Sort and Show rows in
the QBE grid (the grid in the bottom pane of Design view) are removed, and
the Delete row is added. The Delete row has a drop-down menu with two
options that you see only with delete queries: Where and From. Use these
two options to define the fields you want to see and the fields that you’re
using to define criteria to select the fields that will be deleted by the query:
✦ Where: Tells Access to use the criteria for the field to determine which
records to delete.
✦ From: Displays the field when you view the datasheet for the query.
You can choose the From option only when you use the * choice in the
Field row to include all fields from a table. The asterisk appears as the
first field for each table shown in the top half of Design view; when the
asterisk is dragged to the design grid, Access displays all fields from
the table in the query datasheet. Viewing all fields from a table in the
datasheet gives you a more-complete picture of the data you’re
deleting; otherwise, all you see in the datasheet are the values from the fields
included in the design grid with criteria — rather than the entire record
that the delete query will actually delete when you run it.
Follow these steps to create a delete query:
1. In Design view, create a select query that includes all the tables with
records you want to delete.
See Chapter 1 of this minibook for details on creating a select query.
Make sure that you add to the query all tables containing records you
want to delete.