Article ID: 311175
Article Last Modified on 1/31/2007
APPLIES TO
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
This article was previously published under Q311175
SUMMARY
The information covered in this article is provided by: Microsoft Press.
This article is part 8 of a series of eight articles that explain how to keep your information in Access accurate. To view the other articles in this series, please see the "Additional Resources" section later in this article.
This information is an excerpt from the Microsoft Access Version 2002 Step by Step book, Chapter 6: "Keeping Your Information Accurate". Learn More About Microsoft Access Version 2002 Step by Step.
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.
- Open the database you want to use.
- In the Database window, click Queries under Objects.
- Double-click Create query in Design view to open both the query window and the Show Table dialog box.
- 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.
- 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.
- 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.
- From appears in the Delete cell under this field.
- 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.
- In the Criteria cell for the fields that you have dragged to the grid, type the criteria.
- 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.
- Click Run on the toolbar to delete the records.
Access displays a warning to remind you of the permanence of this action.
- 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.
- 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.
- 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.
- Close the query, and then close the database.
ADDITIONAL RESOURCES
Part 1: Introduction Q311167
Part 2: Using the Data Type to Restrict Data Q311168
Part 3: Using the Field Size Property to Restrict Data Q311169
Part 4: Using an Input Mask to Restrict Data Q311171
Part 5: Using Validation Rules to Restrict Data Q311172
Part 6: Using a Lookup List to Restrict Data Q311173
Part 7: Updating Information in a Table Q311174
Part 8: Deleting Information From a Table Q311175
REFERENCES
The information in this article is an excerpt from the Microsoft Access Version 2002 Step by Step book, published by Microsoft Press.
[GRAPHIC: Picture of Access 2000 Step by Step book. ][1]
Learn More About Microsoft Access Version 2002 Step by Step
For more information about this publication and other Microsoft Press titles, see http://mspress.microsoft.com.
Additional query words: kbmspressexcerpt inf kbgraphxlinkcritical ACC2002 reviewdocid
Keywords: kbhowto kbinfo KB311175
![[GRAPHIC: Picture of Access 2000 Step by Step book. ]](/wiki/index.php?title=Special:Upload&wpDestFile=Mspresspilot_acc2000stepbystepbook.gif){kind=link}