Access 101: DELETE Query

This article is part of my Quick Start Guide to Microsoft Access series, Access 101.

The DELETE query is your tool for removing data from your tables.

Like the UPDATE query, it is a powerful tool. Compared to the UPDATE query, though, it has the potential to cause even more destruction. Nearly every one of the caveats we covered for the UPDATE query applies equally to the DELETE query.

DELETE queries are dangerous because of what makes them powerful: the ability to perform set-based operations.

If you're not careful, it's easy to accidentally delete every record in a table with a DELETE query. It can be as simple as executing the query before defining a WHERE condition. In the blink of an eye, you can remove millions of records.

This tool is like Kevin Costner's katana in The Bodyguard:

Handle it with care.

What is a DELETE Query?

In my earlier article, Access 101: What is a Query in Access?, I introduced the CRUD acronym to describe the four basic operations you can perform on data:

Create
Read
Update
Delete

The DELETE query is the tool for the final operation: Delete. It allows you to remove rows of data in bulk from your tables.

The Basic Structure of a DELETE Query

The basic structure of a DELETE query is as follows:

DELETE FROM tableName
WHERE condition;

DELETE is the command that initiates the query.
tableName is the name of the table from which you want to remove records.
WHERE condition is an optional clause that you can use to specify which records you want to remove. The condition is a logical statement that can be used to filter records. BE WARNED: If you omit the WHERE clause, the DELETE query will remove all records from the table.

Multi-Table DELETE Statement in Access

In the above simple format, we were only dealing with a single table.

Sometimes you will want to identify records to delete based on referenced values in other tables. Consider the following example:

DELETE R.* FROM Person AS P
  INNER JOIN Roster AS R ON P.PersonID=R.PersonID
WHERE P.FirstName='Mike'

Person AS P: create an alias ("P") for the "Person" table to improve readability
Roster AS R: create an alias ("R") for the "Roster" table
DELETE R.*: this command tells Access to remove records from the Roster table (with alias "R")

Specifying which table to delete records from using the syntax TableName.* is optional when there is only one table involved in the query. However, with multiple tables, you must be explicit about which table's records you want to remove, otherwise you will get the following error message:

Specify the table containing the records you want to delete.

Executing a DELETE Query in Access

Remember, DELETE queries can permanently remove data from your database, so it's crucial to use them with care.

Before we dive into deleting records, let's talk about previewing the records you plan to delete. This is an essential step to ensure you don't accidentally remove the wrong data.

Previewing Records with a SELECT Query

Before running a DELETE query, you should always run a SELECT query to preview the records that will be deleted. Here's how to do that:

Open your Access database and navigate to the "Create" tab on the Ribbon.
Click on "Query Design" to open a new query.
In the "Show Table" dialog box, double-click the table from which you want to delete records.
Close the "Show Table" dialog box.
Double-click the asterisk (*) in the table to add all fields to the query or select specific fields you want to preview.
In the "Criteria" row under the appropriate field, enter the condition that the records to be deleted must meet.
Click "Run" (!) on the Design tab to execute the SELECT query and view the records that meet your criteria.

Creating and Running a DELETE Query

Once you've confirmed that the SELECT query returns only the records you want to delete, you can convert it to a DELETE query:

Return to the Design view of your SELECT query.
On the Design tab, click on the "Query Type" group and choose "Delete" to change the query type.
The grid at the bottom will change to reflect the DELETE query design. You'll see a "Delete" row, which should say "From" for each field you're deleting.
If you've selected specific fields in the SELECT query, ensure the "Delete" row has "From" only for the asterisk (*) or for the primary key field.
Review the "Criteria" row to ensure it still reflects the correct condition for deletion.
[OPTIONAL]: Before running the DELETE query, save it with a descriptive name by pressing [Ctrl] + [S].
To execute the DELETE query, click "Run" (!) on the Design tab. A warning will pop up, telling you how many rows will be affected. Review this number carefully.
If the number of records to be deleted looks correct, click "Yes" to proceed with the deletion.

Err on the Side of Too Many WHERE Clauses

It's all too easy to run a DELETE query and accidentally remove more records than you intended.

One technique I use–especially when running ad hoc DELETE queries against live data–is to add redundant WHERE clauses. Below is an example from an earlier article I wrote. The specific example is for an UPDATE query, but the same advice applies (for the same reason) to DELETE queries.

Use Excessive WHERE Clauses

If you only intend to change a single record, find that record's primary key and include it in the WHERE clause. Don't rely on a date-vendor combination to update a single invoice record only to find out too late that that vendor had several invoices on that particular date. Instead of changing one record, you accidentally changed a dozen!

For example, instead of this...

UPDATE Invoice
SET PmtAmt = 42
WHERE VendorID = 10
  AND PurchaseDate = '2022-1-12'

...use this...

UPDATE Invoice
SET PmtAmt = 42
WHERE InvoiceID = 1783
  AND VendorID = 10
  AND PurchaseDate = '2022-1-12'
  AND PmtAmt = 52
  AND DeliveredOn Is Null
  AND ProductID = 683

The chances of accidentally updating the wrong record in the second version are close to zero.

When to Use DELETE Queries

I don't use DELETE queries as often as I used to.

I mostly use DELETE queries now as an "undo" for an INSERT INTO query.

Imagine a business application where you are guiding a user through a recurring process. Let's say you are generating monthly billing records. You might run an INSERT INTO query that creates thousands of records with the same billing date. Before sending the bills, though, the user realizes they forgot to apply an annual rate increase.

I would use a DELETE query in that instance to delete the recently created records so the user could apply the rate increase then recreate the bills.

Incorporating my advice from the previous section, I might also include a CreatedAt date-time field as an additional WHERE clause in the DELETE query. And, just to be extra careful, prevent users from deleting records that were created more than some number of days in the past (say, 30) to help them avoid accidentally removing important historical data.

When Not to Use DELETE Queries

Flagging records as "inactive", "disposed", or even "deleted" is often preferable to removing the records altogether.

Keep in mind, DELETE-ing is forever. The nice thing about marking records as inactive without actually removing them is that the action is easier to undo.

There are two basic approaches to marking records as deleted:

Include a boolean (yes/no) field to act as an active flag, like IsActive
Include an optional datetime field to save the time the record was disposed, like InactivatedAt

Marking Inactive Records with a Yes/No Field

A yes/no field is simple and easy to understand. Here are a few of my best practices for this approach:

Use positive field names. Instead of naming the field Inactive and checking the box when it becomes inactive, name the field IsActive and set its default value to True. Positive field names help you avoid double negatives in your queries, like Not Inactive.

Marking Inactive Records with a DateTime Field

While a yes/no field is simple and easy to implement on a form with a checkbox, using an Optional datetime field provides you with the benefit of knowing when a record was "removed" with very little additional marginal cost.

Create an optional field named something like InactivatedAt.
Identify active records with the condition WHERE InactivatedAt Is Null.
Identify inactive records with the condition WHERE InactivatedAt Is Not Null. (Unfortunately, there is no way to avoid the double-negative here.)

The Key to Flagging Records Instead of Deleting Them

This is a decision best made at the outset of a project.

Taking the approach of marking records as having been removed rather than actually deleting them requires you to include this criteria in all the queries and record sources that power your forms, reports, and combo boxes. Making a switch from deleting to flagging in a mature application can be a massive undertaking.

If you plan it from the beginning, though, it's not much extra work.

Best Practices for Using DELETE Queries

Backup First: Always ensure you have a backup of your data before running a DELETE query. Once records are deleted, they cannot be recovered unless you have a backup.

Use a Development Copy: Experiment with DELETE queries on a development copy of your data, not on live production data. This will prevent accidental data loss in your main database.

Test with SELECT: Before executing a DELETE query, run a SELECT query with the same WHERE clause to review which records will be affected. This can help you catch errors in your criteria before they lead to data loss.

Confirm Intent: Implement a confirmation step in your application or process that requires explicit user approval before a DELETE query is executed. This can be a simple message box asking, "Are you sure you want to delete these records?"

Limit Permissions: Restrict the ability to run DELETE queries to users who understand the implications and have the necessary experience. Not every user needs the power to delete data.

Transactional Safety: Consider using transactions when performing DELETE operations. This way, if something goes wrong, you can roll back the entire operation, preventing partial deletions.

Document Your Queries: Keep a record of any ad hoc DELETE queries you run, including the purpose and the criteria used. This documentation can be invaluable for auditing and understanding past actions.

Optimize WHERE Clause: Ensure your WHERE clause is precise to avoid deleting unintended records. Use primary keys or other unique identifiers to target specific records.

Use Relationships with Caution: If your tables have relationships with cascade delete options enabled, deleting records from a parent table will also delete related records from child tables. Understand these relationships before executing a DELETE query.

Consider Alternatives: Sometimes, instead of deleting records, it's better to flag them as inactive or moved. This retains the data for historical analysis and can prevent issues with referential integrity.

In conclusion, the DELETE query is a potent tool in Access that should be used with the utmost care. By following these best practices, you can ensure that you use DELETE queries responsibly, maintaining the integrity and reliability of your data.

Remember, with great power comes great responsibility.