Over time, some types of information in a database can become obsolete. The Products table in The Garden Company database, for example, maintains a list of all the products the company currently offers for sale or has sold in the past. When a product is no longer available for sale, a check mark is placed in the Discontinued field. Discontinued products aren t displayed in the catalog or pushed by salespeople, but they are kept in the database for a while in case it becomes practical to sell them again. A similar situation could exist with customers who haven t placed an order in a long time or who have asked to be removed from a mailing list but might still place orders.
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.
Keep in mind several things when deleting records from a database. First, there is no quick recovery of deleted records. Second, the effects of a delete query can be more far-reaching than you intend. If the table in which 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. There are two solutions to this problem: back up your database before deleting the records; or create a new table (perhaps named Deleted < file name >), and then move the records you want to delete to the new table.
In this exercise, you will create a delete query to remove all discontinued products from the Products table of the GardenCo database.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Office 2003 SBS\Accurate\QueryDel folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Microsoft Office System 2003 Step by Step .
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the Objects bar, click Queries .
Double-click Create query in Design view to open both the query window and the Show Table dialog box.
Double-click Products to add that table to the list area of the query window, and then click Close to close the Show Table dialog box.
Double-click the asterisk at the top of the list of fields to include all the fieldsin the query.
Products.* appears in the Field row of the first column of the design grid,and Products appears in the Table row.
Double-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, when you do it that way 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.
Scroll to the bottom of the field list, and double-click Discontinued to copyit to the next available column in the design grid.
On the Query menu, click Delete Query to convert this select query to a delete query.
You might have to hover over the short menu or click the double “chevrons to see the Delete Query command on the long menu.
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 Products 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 , as you did with the Discontinued field, the Delete row displays Where , indicating that this field can include deletion criteria.
Type Yes in the Criteria row under Discontinued .
The Discontinued field is set to the Boolean data type, which is represented in the datasheet as a check box that has a check mark to indicate Yes and no check mark to indicate No. To locate all discontinued products, you need to identify records with the Discontinued field set to Yes.
To check the accuracy of the query, click the View button.
Access displays a list of 18 discontinued products that will be deleted, but it hasn t actually changed the table yet. Scroll to the right to verify that all records display a check in the Products.Discontinued field.
Click the View button to return to Design view.
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 order 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 the Run button to run the delete query.
Access displays a warning to remind you of the permanence of this action.
Click Yes to delete the records.
Click the View button to see that all the records were deleted.
If you think you might run the same delete query in the future, click the Save button, and name the query. Then close the query.
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.
CLOSE the GardenCo database.