How was your night?
Today, I want to teach how to delete records by using queries on MS Access.
The steps to follow to get this done are:
- Open the Query Designer, and add the Products table to the query.
- In the Products field list, double-click * (the asterisk) to enter * in the Field row and Products in the Table row of the first column of the design grid.
TIP Double-clicking the asterisk in the field list is a quick way to move all the fields to the query, without each field taking up a column in the design grid and possibly making it necessary to scroll from side to side to view them all. However, selecting all the fields in this way prevents you from setting 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 in the results, clear the check box in the Show row of the duplicate individual fields.
In the Products field list, double-click Discontinued to copy it to the next available column in the design grid.
Let’s convert this select query to a delete query and then use criteria to identify the records we want to delete.
On the Design tool tab, in the Query Type group, click the Delete button to convert this select query to a delete query.
In the query design grid, the Sort and Show rows disappear, and a Delete row appears.
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 Yes/No data type, which is represented in the datasheet as a check box that is selected to indicate Yes and cleared to indicate No. To locate all discontinued products, you need to identify records with the Discontinued field set to Yes.
In the Criteria row of the Discontinued field, enter Yes, and then press the Enter
Before we do anything else, let’s test the query.
Without running the query, switch to Datasheet view, which displays the 18 discontinued products that will be deleted if you run the query.
Scroll to the right to verify that for all records, the Products.Discontinued check box is selected.
Switch back to Design view, and run the query.
Before deleting the records, Access warns you of the permanence of this action.
In the message box, click Yes, which displays another warning.
In the message box, click No to cancel the deletion. Two discontinued records cannot be deleted because they have entries in the Order Details table. If this were a real database, you would need to decide whether to set the Discontinued field of these products to No (clear their check boxes) or to delete the entries from the Order Details table before allowing the query to delete the product records.
Suppose you are concerned that someone might accidentally run this delete query and destroy records you aren’t ready to destroy. Let’s change the query back to a select query before saving it.
On the Design tool tab, in the Query Type group, click the Select button. Notice that in the query design grid, the Delete row has disappeared and the Sort and Show rows are now displayed.
Save the query with the name Delete Discontinued Products.
TIP If you want to delete obsolete records in the future, open the select query in Design view, convert it to a delete query, and then run it.
Your questions are welcome.