Part 8 of "Keeping Your Information Accurate" in Access 2003 and Access 2002: "Deleting Information From a Table" (311175)

MORE INFORMATION

Eventually, the time comes to clean house and discard some records. You could do this by scrolling through the tables and deleting records by hand, but if all the records to be deleted match some pattern, you can use a delete query to quickly get rid of all of them.

IMPORTANT: Keep in mind several things when deleting records from a database. The effects of a delete query can be more far-reaching than you intend. If the table where you are deleting records has a relationship with another table and the Cascade Delete Related Records option for that relationship is set, records in the second table will also be deleted. Sometimes this is what you want, but sometimes it isn't. For example, you don't want to delete the records of previous sales just because you're deleting discontinued products. Be sure to back up your database before deleting any records.

1. Open the database you want to use.
2. In the Database window, click Queries under Objects.
3. Double-click Create query in Design view to open both the query window and the Show Table dialog box.
4. Double-click the table you want to use to add that table to the list area of the query window, and then click Close to close the Show Table dialog box.
5. Drag the asterisk (*) from the field list for the table to the query design grid. Or, double-click the asterisk at the top of the list of fields to include all the fields in the query.
   
   TIP: Clicking the asterisk in the field list is a quick way to move all the fields in the table to the query, without having each field appear in its own column. However, then you can't set Sort, Show, and Criteria values for individual fields. To set these values, you have to add the specific fields to the design grid, thereby adding them twice. To avoid displaying the fields twice, clear the check mark in the Show row of the duplicate individual fields.
6. In query Design view, click the arrow next to Query Type on the toolbar, and then click Delete Query.
   In the design grid, the Sort and Show rows have disappeared, and a Delete row has been added. In the first column, which contains the reference to all fields in the table, the Delete row contains the word From, indicating that this is the table from which records will be deleted. When you add individual fields to the remaining columns, the Delete row displays Where, indicating that this field can include deletion criteria.
7. From appears in the Delete cell under this field.
8. To specify criteria for deleting records, drag to the design grid the fields on which you want to set criteria.
   
   Where appears in the Delete cell under this field.
9. In the Criteria cell for the fields that you have dragged to the grid, type the criteria.
10. To preview the records that will be deleted, click View on the toolbar. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.
    
    TIP: Before actually deleting records, you might want to display the Relationships window by clicking Relationships on the Tools menu. If the table you are deleting from has a relationship with any table containing information that shouldn't be deleted, right-click the relationship line, click Edit Relationship on the shortcut menu, and make sure that Enforce Referential Integrity is selected and Cascade Delete Related Records is not selected.
11. Click Run on the toolbar to delete the records.
    
    Access displays a warning to remind you of the permanence of this action.
12. Click Yes to delete the records.
    
    Access displays another warning if it cannot delete any records due to key violations. For example, if you are deleting discontinued products and a discontinued product has been ordered so that it appears in a table for ordered products.
13. Click Yes to run the query. Access displays a warning to let you know that you are about to delete records permanently. Click View to see any items that were not deleted.
14. If you think you might want to run the same delete query in the future, click the Save button and provide a name to save it. Then close the query.
    
    TIP: If you are concerned that someone might accidentally run a delete query and destroy records you weren't ready to destroy, change the query back to a select query before saving it. You can then open the select query in Design view and change it to a delete query when you want to run it again.
15. Close the query, and then close the database.

ADDITIONAL RESOURCES