Delete Queries

1. Delete Records in Design View
2. Delete Records in SQL View

Delete Records Using Query Design View

In the Query Design view, you can drag and drop the tables that you want to delete records from, and then specify the criteria in the Criteria row of the query grid.

To delete records with Query Design view, open the database that contains the records you want to delete and then follow these steps:

1. Go to the Create tab and click the Query Design icon in the Queries group.

2. In the Add Table pane, drag (or double-click) the tables that contain the records you want to delete on the query area.
3. Now you need to tell Access that this is a delete query. Go to the Query Design tab and click the Delete icon in the Query Type group.

Now you have to tell Access what you want to delete.

4. Drag (or double-click) the Job Title field from the table into the design grid.
5. The Where appears in the Delete cell, indicating that the Job Title field will be used as the criteria to select which records will be deleted from the table.
6. Click the Job Title column's Criteria row and type Admin. Access will add the "quotation marks" around the text string Admin for you, as shown in the figure:

That's all there is to creating a delete query. Before you run a delete query, you should always preview the results in the Datasheet view first. Click the View button on the ribbon to display the delete query in the Datasheet view.

Click Run icon on the Query Design tab, Access displays a confirmation box to confirm if you really want the delete query to delete the record. Access silently deletes all the records for Admin.

Delete All Records

If you want to delete all records in the table, drag (or double-click) the asterisk * from the top of the table field list into the design grid. Notice that From appears in the Delete cell for the asterisk field, indicating that the records will be deleted from the table. See the following fig:

If you want to save the delete query, right-click on the query window tab, click Save, type a name for the query, and then click OK.

Delete Records Using SQL

In Microsoft Access, you can use SQL (Structured Query Language) to perform various database operations, including deleting records from a table.

1. Go to the Create tab and click the Query Design icon in the Queries group.

2. Click the down arrow key on the View button and choose SQL View from the menu:

You use the DELETE statement to delete records from a table. Here's the basic syntax for a delete query in MS Access:

DELETE * FROM TableName
WHERE Criteria;

Here's what each part of the SQL statement does:

• DELETE * FROM TableName: This part specifies the name of the table from which you want to delete records. Replace TableName with the actual name of your table.
• WHERE Criteria: This part is optional but allows you to specify criteria that determine which records should be deleted. If you omit the WHERE clause, all records in the table will be deleted. If you include it, only the records that match the specified criteria will be deleted.

Here's an example of a simple delete query that deletes all records from a table called "Customers":

DELETE * FROM Contacts;

Here's an example of a delete query that deletes specific records based on a condition, in this case, deleting all contacts containing job tile Admin:

DELETE * FROM Contacts
WHERE [Job Title]="Admin";

Click Run icon on the Query Design tab, Access displays a confirmation box to confirm if you really want the delete query to delete the record:

Please be cautious when using DELETE queries, especially without a WHERE clause, as they can permanently remove data from your database, and there's no easy way to undo the operation. Always have a backup of your database before running any delete queries, especially on a production database.