Access 2007 for Starters: The Missing Manual

Delete Queries

Delete queries are the simplest—and most dangerous—of the action queries. A delete query works much like a normal select query. You specify a set of filter conditions, and then Access finds the matching records in the table. But the delete query doesn't just display the records—instead, it erases them from the database.

Delete queries are great for clearing out a huge number of records at once after you've finished transferring them to another table. In the append-query example described earlier (Section 7.3.1), you probably want a way to remove the original records once you've copied them to the new table. A delete query fits the bill perfectly.

To create a delete query, follow these steps:

1. Create a new query by choosing Create → Other → Query Design.

2. Using the Show Table dialog box that appears, add the table that has the records you want to delete. Then click Close to close it.

3. Change your query to a delete query by choosing Query Tools | Design → Query Type → Delete.

   The Sort and Show boxes disappear from the column list, and the Delete box appears.

4. Add the fields you want to use for filtering, and then set your filter conditions.

   Your filter conditions determine what records are deleted, so make sure you define them carefully. If you don't include any filter conditions—gulp—Access will delete all the records when you run the query.

5. Add any other fields that you want to inspect to confirm you're getting the correct records in the datasheet preview.

   It's critical ...