Use queries to delete one or more records from a database

This article explains how to delete data from an Microsoft Office Access 2007 database by using update and delete queries. You use an update query to delete individual fields from the records in a database, and you use a delete query when you need to remove entire records from a database, including the key value that makes the record unique. As you proceed, remember that Access also provides a number of ways to delete part or all of a record manually.

For more information about deleting records manually, see the article Delete one or more records from a database.

In this article

Understand using queries to delete data

Plan the deletion

Stop Disabled Mode from blocking a query

Delete partial records (one or more fields)

Delete entire records

Sample criteria for select queries

Understand using queries to delete data

You can use two types of queries to delete data from an Access database. The query that you use depends on the type of deletion that you need to perform.

• When you need to delete individual fields from the records in one or more tables, you use an update query that changes the existing values to either a null value (that is, no data) or a zero-length string (a pair of double quotation marks with no space between them).

  You use an update query to delete data in individual fields because, by default, delete queries only remove entire rows. The process of using an update query to "delete" data follows these broad steps: Create a select query and ensure that it returns only the data that you want to delete. You then convert the query to an update query, using either NULL or a zero-length string as the update value, and then run the query to update the existing data.

• When you need to remove entire records (rows), you use a delete query.

  By default, delete queries remove all of the data in each field, along with the key value that makes a record unique. When you run the query, it always removes the entire row from the table. The process follows these broad steps: Create a select query, ensure that it returns the records that you want to delete, and then convert the query to a delete query and run it to remove the data.

As you proceed, remember that if the records that you want to delete reside on the "one" side of a one-to-many relationship, you must first alter a property of the relationship before you run the query. Preparations for deleting related data, later in this article, explains how to perform that task. You must make the additional changes because by default, Access prevents you from deleting data on the "one" side of a one-to-many relationship.

If you need more information about one-to-many relationships, see the articles Database design basics and Create, edit or delete a relationship.

Also, remember that after you update or delete part or all of a record, you cannot undo your changes, so you should always back up your data before you run a delete query. See Back up your database, later in this article, for more information.

The procedures in the following sections explain how to create update queries that remove data from individual fields, and how to create delete queries that delete entire records.

Note: Typically, you use update and delete queries only when you need to change or remove large amounts of data quickly. If you want to remove a small number of records — any quantity that you feel comfortable deleting by hand — you can open the table in Datasheet view, select the fields or rows that you want to delete, and press DELETE.

For more information about other ways to delete data, see the article Delete one or more records from a database.

Top of Page

Plan the deletion

The process of using a query to delete records follows these general steps:

• Plan the deletion. Make sure that you have the necessary permissions to delete the data, ensure that all other users have closed any objects that use the affected tables, and then back up the database.

• Make sure that the database resides in a trusted location, or that it is digitally signed. If neither of these conditions apply, you can choose to trust the database for the current session only. Remember that, by default, Access blocks all action queries (delete, update, and make-table queries) unless you first trust the database. For information about trusting a database, see Stop Disabled Mode from blocking a query, later in this article.

• If you want to delete data from more than one table, and those tables are related, you enable the Referential Integrity and Cascade Delete Related Records options for each relationship. This allows your query to delete data from the tables on the "one" and "many" sides of the relationship.

• Create a select query, and then add criteria as needed until the query returns the correct set of records.

• If you want to delete individual fields from a table, convert the select query to an update query, enter NULL or a zero-length string ("") as the update criteria, and run the query.

• If you want to delete whole records, convert your select query to a delete query, and then run the query.

General preparations

Do the following tasks before you delete partial or full records from a database:

• Ensure that the database is not read-only. To do so, in Windows Explorer, right-click the database file (the .accdb or .mdb file), and then click Properties on the shortcut menu. In the Properties dialog box, ensure that the Read-only check box is cleared.

• Verify that you have the necessary permissions to delete records from the database. If you are not sure, contact your system administrator or the database designer.

• Check with other users of the database to ensure that the deletion will not affect them adversely.

• Ask all users of the database to close all tables, forms, queries, and reports that use the data that you want to delete. This helps avoid lock violations.

  Tip: If a large number of users connect to the database, you might need to close the database and then reopen it in exclusive mode. To do so:

  1. Click the Microsoft Office Button , and then click Open.

  2. Browse to and select the database, click the arrow next to the Open button, and then click Open Exclusive.

     

• Back up your database before you edit or delete records. Some delete operations can be reversed, but making a backup copy ensures that you can always reverse your changes.

  Back up your database

  1. Click the Microsoft Office Button , point to Manage, and then, under Manage this database, click Back Up Database.

  2. In the Save As dialog box, specify a name and location for the backup copy, and then click Save.

     Access closes the original file, creates a backup, and then reopens the original file.

     To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and open the renamed backup copy in Access.

Preparations for deleting related data

If you need to delete data in related tables, keep this rule in mind: if the data resides on the "many" side of a one-to-many relationship, you can delete the data without having to alter the relationship. However, if the data resides on the "one" side of a one-to-many relationship, you must first alter the relationship or Access blocks the deletion.

The process of deleting related data follows these broad steps:

• Determine which records reside on the "one" side of the relationship and which reside on the "many" side.

• If you need to delete records on the "one" side of the relationship and the related records on the "many" side, you enable a set of rules called Referential Integrity, and you enable cascading deletes. Steps in this section explain Referential Integrity, and how to perform both tasks.

• If you need to delete records only on the "one" side of the relationship, you first delete that relationship, and then delete the data.

  -or-

  If you need to remove data only on the "many" side of the relationship, you can create and run your delete query without having to change the relationship.

The steps in the following sections explain how to prepare related data for deletion.

Determine which records reside on the "one" and "many" sides of a relationship

1. On the Database Tools tab, in the Show/Hide group, click Relationships.

   The Relationships tab appears and displays all of the tables in your database and also the relationships between each table and every other table. Each relationship is depicted as a line that connects tables between fields.

   The following figure shows a typical relationship. Most, if not all, of the relationships in a database have a "one" side and a "many" side. The relationship diagram denotes the "one" side with the numeral one (1) and the "many" side with the infinity (∞) symbol.

   

   As a rule, when you delete records on the "one" side of the relationship, you also delete all of the related records on the "many" side of the relationship. However, when you delete records on the "many" side of a relationship, you typically don't delete records on the "one" side.

   Also, by default, Access enforces a set of rules called referential integrity. These rules ensure that the foreign keys in a database contain the correct values. A foreign key is a column whose values match the values in the primary key column of another table.

   Learn about the referential integrity rules

   • Rule 1: You can't enter a value in the foreign key field of a related table unless that value already exists in the primary key of the primary table. However, you can enter a null value in the foreign key field.

   • Rule 2: You can't delete an entire record from a primary table if matching records exist in a related table.

     Remember that you can work around this rule by enabling a property in the relationship between the primary and secondary tables. For more information about how to do so, see the next procedure, Edit the relationship.

   • Rule 3: You can't change a primary key value in the primary table if that record has related records.

     However, you can also work around this rule by enabling a property in the relationship between the primary and secondary tables.

     Unless you dictate otherwise, Access enforces these rules each time that you add, edit, or delete data in the database. When an action violates a rule, Access displays a message, like the one shown in the following illustration, and cancels the action.

     

   As you proceed, remember that, although Access enables referential integrity by default in most cases, a database must first meet the following conditions:

   • The matching field from the primary table must either be a primary key or have a unique index.

   • The related fields in the primary and related table must both have the same data type.

     Note: Access provides two exceptions to this rule. An AutoNumber field can be related to a Number field if that Number field has a FieldSize property setting of Long Integer. Also, an AutoNumber field that has a FieldSize property setting of Replication ID can be related to a Number field that has a FieldSize property setting of Replication ID.

   • Both tables must belong to the same Access database.

     Note: If the tables are linked, they must be in Access format. Also, you must open the database that contains the linked tables and then enable referential integrity. You cannot enforce referential integrity rules for linked tables from databases in other formats, such as Microsoft Office Excel 2007 workbooks. For information about enabling referential integrity, see the steps in the next section.

2. Note the names of the table fields on each side of the relationship.

3. Open each table, and review the data in each field to verify that the fields do contain the data that you want to delete.

4. Leave the Relationships pane open, and then go on to the steps in the next section.

Edit the relationship

Follow these steps only when you need to delete data on the "one" and "many" sides of a relationship.

1. If you haven't done so already, open the Relationships pane.

   On the Database Tools tab, in the Show/Hide group, click Relationships.

2. Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Edit Relationship on the shortcut menu.

   The Edit Relationships dialog box appears.

3. Ensure that the Enforce Referential Integrity check box is selected.

4. Select the Cascade Delete Related Records check box.

   Note: Until you disable this property again, deleting a record on the "one" side of the relationship will delete all of the related records on the "many" side of the relationship.

5. Click OK, close the Relationships pane, and then go on to the next set of steps.

Delete a relationship

1. If you haven't done so already, open the Relationships pane.

   On the Database Tools tab, in the Show/Hide group, click Relationships.

2. Note the fields involved in the relationship so that you can go restore the relationship after you delete your data.

3. Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Delete on the shortcut menu.

   -or-

   Select the relationship and press DELETE.

Note: To restore the relationship, follow the previous steps to open the Relationships pane, and then drag the primary key field from the "one" table and drop it on the foreign key field of the "many" table. The Edit Relationship dialog box appears. If the old relationship enforced referential integrity, select Enforce Referential Integrity, and then click Create. Otherwise, just click Create.

Top of Page

Stop Disabled Mode from blocking a query

By default, if you open a database that you have not chosen to trust or that does not reside in a trusted location, Access blocks all action queries from running.

If you try to run an action query and it seems like nothing happens, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

When you see that message, take the following steps:

Enable blocked content

• On the Message Bar, click Options.

  The Microsoft Office Security Options dialog box appears.

• Click Enable this content, and then click OK.

• Run your query again.

If you don't see the Message Bar

• Click the Database Tools tab, and in the Show/Hide group, click Message Bar.

For more information about Disabled mode and Access security, see the article Secure an Access 2007 database.

Delete partial records (one or more fields)

The steps in this section explain how to use an update query to delete partial records (individual fields) from tables on the "many" side of a one-to-many relationship. You can also follow these steps to delete data from tables that are not related to other data. Remember that running an update query to delete data changes your existing values to either NULL or a zero-length string (a pair of double quotation marks with no space in between), depending on the criteria that you specify. For more information about using update queries, see the article titled Create and run an update query.

Create a select query

1. On the Create tab, in the Other group, click Query Design.

   The query designer opens, the Design tab appears, and the Show Table dialog box appears.

2. Select the table that contains the data you want to delete, click Add, and then click Close.

   The table appears as a window in the upper section of the query design grid. The window lists all of the fields in the table. This figure shows a typical table in the designer.

   

3. Add the fields that you want to set to NULL on the Field row of the designer. You can double-click each field or drag and drop each field.

4. Optionally, enter criteria for one or more fields in the Criteria row of the designer.

   You use criteria to return only the records that you want to delete. Otherwise, the update query sets to NULL every record in each of the fields in your query. For more information about using criteria, see Sample criteria for select queries, later in this article.

5. On the Design tab, in the Results group, click Run.

   Verify that the query returns the records that you want to set to NULL or a zero-length string. As needed, repeat steps 3-5 and alter your fields or criteria until the query returns only the data that you want to change.

6. Keep the query open, and then go on to the next steps.

Convert the select query into an update query

1. Click Design View to switch from the datasheet to the query designer.

2. On the Design tab, in the Query Type group, click Update.

   Access changes the select query to an update query, hides the Show row in the lower section of the design grid, and adds the Update To row.

   In the Update To row for each field, enter either NULL or a zero-length string — a pair of double-quotation marks with no space between them ("").

3. Click Run .

   Access prompts you to confirm the changes.

   Be very sure that you want to change the values, and then click Yes to change the data.

Top of Page

Delete entire records

The steps in this section explain how to use a delete query to delete entire records from a table.

Create a select query

1. On the Create tab, in the Other group, click Query Design.

   The query designer opens, the Design tab appears, and the Show Table dialog box appears.

2. Select the table on the "one" side of the relationship, click Add, and then click Close.

   The table appears as a window in the upper section of the query design grid. The window lists all of the fields in the table. This figure shows a typical table in the designer.

   

3. Double-click the asterisk (*) to add all of the fields in the table to the design grid.

   Adding all the table fields enables the delete query to remove entire records (rows) from the table.

4. Optionally, add a table field that allows you to enter criteria.

   For example, suppose that a customer goes out of business and you need to remove all of the pending orders for that customer. To find just those records, you add the Customer ID and Order Date fields to the design grid.

5. If you followed the preceding step, enter your criteria in the Criteria row of the designer grid.

   You use criteria to return only the records that you want to delete. Otherwise, the delete query removes every record in the table. To continue the example from the preceding step, you enter the ID number of the customer that went out of business, and the date on which that customer's orders became invalid.

   For more information about using criteria, see Sample criteria for select queries, later in this article.

6. If you followed the preceding step, clear the Show check box for each criteria field.

7. On the Design tab, in the Results group, click Run.

   Verify that the query returns the records that you want to delete.

8. Keep the query open, and then go on to the next steps.

Convert the select query into a delete query and delete data

1. Click Design View to switch from the datasheet to the query designer.

2. On the Design tab, in the Query Type group, click Delete.

   Access changes the select query to a delete query, hides the Show row in the lower section of the design grid, and adds the Delete row.

   Ensure that the Delete row in the * (all fields) column displays From. The word Where should appear in any columns that you use for criteria.

3. Ensure that you want to delete the data, and then click Run .

   Access prompts you to confirm the deletion.

   Click Yes to delete the data.

Top of Page

Sample criteria for select queries

The following table lists some sample criteria that you can use in select queries to ensure that you delete only the correct data. You can adapt these samples for use with your own data. Some of these examples use wildcard characters.

For more information about using wildcard characters, see the article Access wildcard character reference.

Criteria	Effect
> 234	Returns all numbers greater than 234. To find all numbers less than 234, use < 234.
>= "Cajhen"	Returns all records from Cajhen through the end of the alphabet
Between #2/2/2007# And #12/1/2007#	Returns dates from 2-Feb-2007 through 1-Dec-2007 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotation marks (') instead of pound signs (#). Example: Between '2/2/2007' And '12/1/2007'
Not "Germany"	Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".
Not "T*"	Finds all records except those beginning with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
Not "*t"	Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
In(Canada,UK)	In a list, finds all records containing Canada or UK.
Like "[A-D]*"	In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
Like "*ar*"	Finds all records that include the letter sequence "ar". If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
Like "Maison Dewe?"	Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark (?).
#2/2/2007#	Finds all records for February 2, 2007. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/2007').
< Date() - 30	Uses the Date function to return all dates more than 30 days old.
Date()	Uses the Date function to return all records containing today's date.
Between Date() And DateAdd("M", 3, Date())	Uses the Date and the DateAdd functions to return all records between today's date and three months from today's date.
Is Null	Returns all records that contain a null (blank or undefined) value.
Is Not Null	Returns all records that contain any value (that are not null).
""	Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what the actual value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, instead of entering a number, you enter a pair of double quotation marks with no space between them ("").

Top of Page