Deleting Records with a Delete Query | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

As you might expect, delete queries remove records from tables. Keep in mind that if you run a delete query, the query will delete the entire record, not just the field you specify in a criteria expression. This will become clearer when we look at a delete query's SQL equivalent later in this section.

Don't Neglect Your Relationships

Relationships are critically important to a delete query. In the sidebar "Updating Both Sides of a Relationship," you learned how to use a relationship to automatically update foreign key values in the "many" side of that relationship by making a change to the primary key value in the "one" side. The same behavior is possible when you run a delete query-which might or might not be exactly what you want. Using the right tools for the job will make it easier for you to troubleshoot any problems down the road.

To use a simple delete query to delete order records more than four years old based on the current date, follow these steps:

Create a copy of the tables you're about to modify. In this case, that would be the Orders and Order Details tables. (Order details are explained a bit later in this section.)
Open the Relationships window by clicking Relationships on the Database toolbar. If there's no relationship between the Orders and Order Details tables, you need to create one. Drag OrderID from the Orders field list to the OrderID field in the Order Details field list. In the resulting Edit Relationships dialog box, select Enforce Referential Integrity and Cascade Delete Related Records, as shown in Figure 10-11. Click Create, and then close and save the Relationships window.
Figure 10-11. Turn on the cascading delete option.
In the Database window, click the Orders table, choose Query from the New Object button's drop-down list, and click OK in the New Query dialog box.
Drag the Order ID and OrderDate fields to the design grid, and add the following expression to the OrderDate column's Criteria cell (as shown in Figure 10-12):
```
 <DateAdd("yyyy",-4,Now()) 
```
Figure 10-12. The expression limits the query to records that are at least four years old.

tip

The DateAdd function lets you make calculations on Date/Time fields. To open a Help topic explaining this function, type DateAdd in the Ask A Question box in the Visual Basic Editor window, and select the DateAdd Function Help topic.
The OrderID field isn't essential to the query, but it's useful in this case. Before running the query, choose Datasheet View from the View button's drop-down list to see the results of the query. Figure 10-13 shows the outcome of the query.
Figure 10-13. Check the query in Datasheet view before you permanently delete the records.

Make a note of the first few and last few order numbers so that you can confirm later that the query actually deleted those records. Don't worry if your order numbers are different from the ones in the figure-the exact orders returned depends on the current date (Now). Notice that the record navigation bar displays the number of records the query will delete-in this example, that's 258 records.
Return to Design view, and choose Query, Delete Query.
Click Run on the Query Design toolbar to execute the query.
Click Yes when Access displays the confirmation message shown in Figure 10-14, indicating the number of records that will be deleted.
Figure 10-14. Click Yes to run the query.
Open the Orders table and confirm that the orders displayed in step 5 were actually deleted from the table.

Remember when I mentioned how important relationships were when running delete queries and you made a copy of the Order Details table? You're about to learn why. Open the Order Details table. As you can see in Figure 10-15, Access deleted all the records related to the orders we just deleted, because the relationship forces cascading deletes.

figure 10-15. records are missing from this table.

Figure 10-15. Records are missing from this table.

Now let's look at the delete query's SQL statement, shown here:

 DELETE Orders.OrderID, Orders.OrderDate FROM Orders WHERE (((Orders.OrderDate)<DateAdd("yyyy",-4,Now())));

Notice that the statement identifies only the two fields that we specified in the design grid: OrderID and OrderDate. However, the query deleted the entire record. It makes no difference whether you specify a single field or all the fields in a delete query; the query will delete the entire record, not just the data in the specified fields. This makes the DELETE statement less readable than some of the other SQL statements, but the logic behind the behavior is sound. If you want to delete data from a field rather than deleting an entire record, you should run an update query to update the field to a zero-length string ("") or Null. (See "Changing Data with an Update Query.")