|
|
|
|
|
|
|
|
|
|
|
| Microsoft® Access Version 2002 Step by Step |
|
|
|
|||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||
Chapter 6: Keeping Your Information Accurate continued
Deleting Information from a Table
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, for whatever reason, 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.
IMPORTANT:
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 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. 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. The working folder for this exercise is \SBS\Access\Accurate\QueryDel. Follow these steps:
- Open the GardenCo database located in the working folder.
- 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 fields in the query.
- Scroll to the bottom of the field list, and double-click Discontinued to copy it 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.
- Type Yes in the Criteria row under Discontinued.
- To check the accuracy of the query, click the View button.
- Click the View button to return to Design view, confident that you have identified the correct records.
- Click the Run button to run the delete query.
- Click Yes to delete the records.
- Click Yes to run the query, and then click View to see the two discontinued products 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.
- Close the query, and then close the database.
- If you are not continuing on to the next chapter, quit Access.
Products.* appears in the Field row of the first column of the design grid, and Products appears in the Table row.
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 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.
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. So to locate all discontinued products, you need to identify records with the Discontinued field set to Yes.
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.
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 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.
Access displays a warning to remind you of the permanence of this action.
Access displays another warning, stating it can’t delete two of the records due to key violations. This is because two discontinued products have been ordered, and so are in the Order Details table. This table has a one-to-many relationship with the Products table, and Enforce Referential Integrity is set between the two tables.
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.
| Top of Page |
Last Updated: Saturday, July 7, 2001