| Often you might have to delete records from a table. For example, you might want to delete records for canceled orders or for customers that have made no purchases for several years. Deleting records from a table with a delete query is the reverse of the append process. You create a select query with all fields (using the * choice from the field list) and then add the individual fields to be used to specify the criteria for deleting specific records. If you don't specify any criteria, Access deletes all the table's records when you convert the select query into a delete query and run it against the table. Tip It's a good practice to run a select query to display the records that you are about to delete and then convert the select query to a delete query.
To give you some practice at deleting records you stop short of actual deletion in this case suppose that Northwind Traders' credit manager has advised you that Austrian authorities have declared Ernst Handel (CustomerID ERNSH) insolvent and that you are to cancel and delete any orders from Ernst Handel not yet shipped. To design the query that selects all of Ernst Handel's open orders, follow these steps:  Open a new query in Design view and add the Orders table to it.
Drag the * (all fields) item from the field list to the Field cell of the query's first column. Drag the CustomerID field to the second column's Field cell. You need this field to select a specific customer's record. The fields that make up the query must be exactly those of the Orders table, so clear the Show box to prevent the CustomerID field from appearing in the query's result twice. This field is already included in the first column's * indicator. In the CustomerID field's Criteria cell, type ERNSH to represent Ernst Handel's ID. A Null value in the ShippedDate field indicates orders that have not shipped. Drag the ShippedDate field from the field list to the third column's Field cell. Click the Show box to prevent the ShippedDate field from appearing in the select query's result twice, because the * in the first column also includes that field. In the ShippedDate field's Criteria cell, type Is Null. To ensure that you delete only records for Ernst Handel and only those that have not been shipped, you must place this criterion on the same line as that of the CustomerID field (see Figure 13.9). Figure 13.9. The test select query design returns all unshipped orders for CustomerID equal to ERNSH.   Run the select query to display the records to delete when the delete query runs (see Figure 13.10).
Figure 13.10. The select query displays the two orders for Ernst Handel that haven't been shipped. 
To proceed with the simulated deletion, which would delete the Order Details records for the two orders, follow these steps:   Click the toolbar's Database Window button to activate the Database window and then click the Tables button to display the table list. Create a copy of the Orders table by clicking the Orders table entry and pressing Ctrl+C to copy the table to the Clipboard. Press Ctrl+V to open the Paste Table As dialog. Type tblOrders as the name of the new table copy, and press Enter.
Repeat step 1 for the Order Details table, naming it tblOrderDetails. These two tables are backup tables in case you actually delete the two records for Ernst Handel. The relationship between the Orders table and its related Order Details table specifies Cascade Delete Related Fields but not Cascade Update Related Fields. Open your select query in design mode and choose Query, Delete Query. Access then replaces the select query grid's Sort and Show rows with the Delete row, as shown in Figure 13.11. The From value in the Delete row's first column, Orders.*, indicates that Access will delete records that match the Field specification from the Orders table. The Where values in the remaining two cells indicate fields that specify the deletion criteria. Figure 13.11. Specifying a delete query adds a Delete row to the grid that identify From and Where fields.  Click the Run button. A message box asks you to confirm the deletion of the rows. Click No to prevent the deletion. Close, and save your query if you want.
Note Deleting records in a one table when records corresponding to the deleted records exist in a related many table violates the rules of referential integrity; the records in the many table would be made orphans. In this situation, referential integrity is enforced with cascading deletions for the Order Details and Orders table. If you delete the two ERNSH records, Jet first deletes the corresponding Order Detail records and then deletes the Orders records.  | By default, Access uses a transaction when applying action queries to multiple tables. In this example, if the query can't delete the two Orders records and their seven related Order Details records, the transaction is rolled back, and no deletions occur. Otherwise, the transaction commits and permanently deletes all base and related records. If you set the action query's Use Transactions property value to No, the query deletes any records it can without violating referential integrity rules. |
If you accidentally delete records for Ernst Handel, reverse the process that you used to make the backup tables: copy the backup tables tblOrders and tblOrderDetails to Orders and Order Details, respectively. You use the tblOrders table in the following section. Jet SQL Following is the Jet SQL statement for the sample delete query: DELETE Orders.*, Orders.CustomerID, Orders.ShippedDate FROM Orders WHERE Orders.Customer AND Orders.ShippedDate) Is Null; The field list is optional for delete queries, but you must have at least one field in the field list to satisfy the Jet query designer. If you delete the field list in SQL view, the query executes, but won't open in Query Design view. Like all other Jet action queries, the Jet SQL and T-SQL statements are identical. |
|
