|Figure 1: The Contact List upon which |
we shall run our Delete Query.
- Select the CREATE TAB of the Access Ribbon.
- Click the QUERY DESIGN icon. It is located in the OTHER group of the CREATE ribbon.
- Select tblContacts from the SHOW TABLE dialogue box.
- Drag the asterix (*) from tblContacts down to the first column of the DESIGN GRID. This is a way of getting the query results to display all fields from the table without having to select each one individually.
- Then Drag the Company field from tblContacts down to the second column of the grid. We have added this field separately because we are going to enter a criteria in this column.
- Click on the CRITERIA row of the Company column, and add the criteria: "Company 5"
|Figure 2: The Select Query created in the|
first stage of the Delete Query.
|Figure 2: The results from the Select Query.|
- If you look at the QUERY TYPE group of the DESIGN ribbon, you will notice that the SELECT QUERY icon is highlighted orange. We need to change this to DELETE QUERY. To do this just click the DELETE QUERY icon further along the group.
Figure 3: The QUERY TYPE group of the DESIGN ribbon.
The DELETE QUERY icon is highlighted orange.
- After the clicking the DELETE icon, you will notice that the row of SHOW tick boxes disappears from the DESIGN GRID, along with the row for SORT. A new row entitled DELETE has taken their place. Access has filled in the values of FROM and WHERE in the first and second columns respectively. These are SQL Keywords: the FROM keyword indicates the first column contains fields from tblContacts, and WHERE indicates the Company column contains a criteria against the data stored in this field.
Figure 4: The QUERY DESIGN GRID for our
DELETE Query. Notice the new row for DELETE
containing the SQL FROM and WHERE
- Click RUN from the QUERY RESULTS group.
- Click YES when prompted whether we want to delete the number of rows matching our query criteria. This will be four rows for the dataset we have been working with.
|Figure 5: The tblContacts table after Company 5|
contacts have been removed.