Delete one or more records from a database

Deleting inaccurate or obsolete data can make your Access database faster and easier to use. For example, if you move data from the previous calendar or fiscal quarter to a set of archive tables in another database, you can reduce the size of your working database and help it run faster. If you remove inaccurate or duplicate data, you can reduce business costs.

This article explains how to delete data from an Access database. It also explains how to delete data from individual fields — a relatively simple process, and how to delete entire records — a process that can be much more complex.

Note: To understand and use the information in this article, you must first understand the concepts of primary and foreign keys. For introductory information about primary and foreign keys, see the article Database design basics.

In this article

About deleting data

Delete the data in a field

Delete the data in a list

Delete unrelated records from a database

Delete related records from a database using a delete query

About deleting data

Access provides several types or levels of data deletion. You can delete data from individual data points from a record, you can delete entire records from a table, and you can also delete entire tables.

You delete individual values when you want to make some or all the data points in a record blank but still leave the record itself (the row) in place in the table. Deleting a data value is relatively easy — you select part or all of the data in the field and press DELETE.

When you delete an entire record, you remove all of the data in each field as well as the key value that makes the record unique. This process removes the entire row from the table. You can delete individual records manually, or you can create delete queries that remove large numbers of records at once.

If the records you want to delete are not related to any other data in the database, the deletion process is fairly simple. You can select an entire row and press DELETE, or you can create and run a delete query. However, if the records to be deleted are related to other data, and if they reside on the "one" side of a one-to-many relationship, you must perform some additional steps — because by default, Access does not allow you to delete related data. Also, remember that once you delete a whole record, you cannot undo that deletion. For that reason, you should always back up your database before you delete data.

Deleting entire tables is not as complex a process as deleting data from fields or deleting entire records, but remember that when you delete an entire table, you can break the functionality of some — and possibly all — of your database if you remove the table incorrectly. In addition, you lose all the data in the deleted table permanently. For those reasons, you should always back up your database before you delete a table.

Finally, you can delete data in less time and with greater accuracy if you understand relational database design. If you are new to Access or to relational databases in general, see Database design basics.

The procedures in the following sections explain how to delete data from individual fields, how to delete entire records, and how to delete entire tables. Each section provides the background information that you need to complete a given task.

Delete the data in a field

You can delete the data in individual fields when you have tables or queries open in Datasheet view Button image , and when you have data entry forms open in Form Button image and Layout Button image views.

Delete data from a datasheet

  1. In the Navigation Pane, double-click the table or query that you want to use. This opens the table or query in Datasheet view.

  2. Select the data that you want to delete.

    You can highlight part or all of the data, or just position the cursor in the field.

  3. If you select all of the data in the field, press DELETE.

    -or-

    On the Home tab, in the Records group, click Delete.

    -or-

    If you place the cursor in the field, press DELETE or BACKSPACE as needed.

Delete data from a form

  1. In the Navigation Pane, double-click the form that you want to use. This opens the form in Form view.

    -or-

    In the Navigation Pane, select the form that you want to use, and on the Home tab, in the Views group, click View, and then click Layout View.

  2. Select the data that you want to delete.

    You can highlight part or all of the data, or just position the cursor in the field.

  3. If you select all of the data in the field, press DELETE.

    -or-

    On the Home tab, in the Records group, click Delete.

    -or-

    If you place the cursor in the field, press DELETE or BACKSPACE as needed.

    Notes: Access might display an error message and prevent you from deleting data. Several factors can stop the deletion:

    • By design, you cannot delete data from some types of queries    For example, you cannot edit the data returned by a crosstab query, and you cannot edit or remove calculated fields — values that a formula calculates as you use your database, but that do not reside in a table. If your form is based on a query that can't delete data, you won't be able to delete data from the form.

    • You do not have the necessary permissions to delete data     Contact your system administrator or database designer.

    • The field does not accept blank or null values     If you have the necessary permissions, open the table in Design view, and then change the Required table property from Yes to No.

    • You are trying to delete information from a list     In a datasheet for a form, you can always spot a list because Access places a down arrow at one end, like so: A blank lookup list . For information on deleting the values in a list, see the next section of this article.

Top of Page

Delete the data in a list

You can delete data from two types of lists: value lists and lookup lists. Value lists display a set of predefined values — values that you or the database designer enter manually. In contrast, lookup lists use a query to retrieve values from another table or another query, and those returned values then populate the list.

The items in a value list reside in the same table as the other values in a record. In contrast, the data in a lookup list resides in one or more other tables. To remove data from a value list, you open the table and edit the items.

Removing data from a lookup list requires additional steps, and those steps vary depending on whether the query for the lookup lists takes its data from a table or another query. If the query for the lookup list is based on a table, you identify that table and the field that contains the data that appear in the list. You then open the source table and edit the data in that field.

If the query for the lookup list is based on another query, you must open that second query, find the source table and field from which the second query takes its data, and change the values in that table.

The following procedures explain how to delete the data in both a value list and a lookup list.

Remove data from a value list

  1. In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.

  2. In the upper section of the design grid, select the table field that contains the value list.

  3. In the lower section of the grid, click the Lookup tab, and then locate the Row Source property.

    By default, double quotation marks surround the items in a value list, and semicolons separate each item: "Excellent";"Fair";"Average";"Poor"

  4. As needed, remove the items from the list. Remember to delete the quotation marks that surround each deleted item. Also, do not leave a leading semicolon, do not leave pairs of semicolons together (;;), and if you remove the last item in the list, delete the final semicolon.

    Note: If you delete an item from a value list, and records in the table already use that deleted item, the deleted item remains part of the record until you change it. For example, suppose your company has a warehouse in City A, but then sells that building. If you remove "City A" from the list of warehouses, you will see "City A" in your table until you change those values.

Remove data from a lookup list

  1. In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.

  2. In the upper section of the design grid, select the lookup field.

  3. In the lower section of the design grid, click the Lookup tab and locate the Row Source Type and Row Source properties.

    The Row Source Type property should display Table/Query, and the Row Source property must contain a query that references a table or another query.

    Note: Queries for lookup fields always begin with the word SELECT.

    Typically (but not always), a select query uses this basic syntax: SELECT [table_or_query_name].[field_name] FROM [table_or_query_name].

    In this case, the query contains two clauses (SELECT and FROM). The first clause refers to a table and to a field in that table; conversely, the second clause refers only to the table. A point to remember: The FROM clause will always tell you the name of the source table or query. SELECT clauses might not always contain the name of a table or a query, although they always contain the name of at least one field. However, all FROM clauses must refer to a table or query.

  4. Do one of the following:

    • If the query in the Lookup field references another query, click the Build button (On the Data tab of the property sheet, click Builder button next) to open the query in Design view. Make note of the name of the table that appears in the upper section of the query designer, and then continue to step 5.

    • If the query in the Lookup field references a table, make note of the name of the table, and then continue to step 5.

  5. In the Navigation Pane, double-click the source table to open it in Datasheet view.

  6. Locate the field that contains the data used in the lookup list, and then edit that data as needed.

Top of Page

Delete unrelated records from a database

When you delete whole records from a database, you delete the data in each field, and you also delete the key value — the value that makes the record unique. If the record is not related to any other records (that is, if the key value does not reside in any other records or tables in the database), you can delete the record by selecting it and pressing DELETE. If you want to delete a large number of unrelated records — more than you want to delete manually — you can use a delete query to remove them. The procedures in the following sections explain how to perform both tasks.

Delete an unrelated record manually

  1. In Datasheet view, open, the table or query that contains the data that you want to delete.

  2. Locate the record (the row) that you want to delete, and then click the Select All button — the square located at the left or right end of the record. You can select individual rows, or click and drag to select a contiguous set of rows, or press CTRL and click the mouse button to select multiple non-contiguous rows.

    Access places a border around the entire record (the entire row) or block of selected records.

  3. Press DELETE.

    -or-

    On the Home tab, in the Records group, click Delete.

    -or-

    Right-click the Select All button and click Delete Record.

    If you can't click the Select All button, position the cursor in a field in the record. Then, on the Home tab, in the Records group, click the down arrow next to Delete and click Delete Record.

    Note: At this point, Access might display an error message that says that you can't delete the record because it is related to other data. If you see such a message, click OK and go to Delete related records from a database using a delete query, later in this article.

  4. When Access prompts you to confirm the deletion, click Yes.

Delete unrelated records by using a delete query

When you use a delete query to remove data, you follow the same process for both unrelated and related records — you first create a select query, then ensure that it returns only the records that you want to delete, and finally convert the select query into a delete query. The background information and steps in the next section explain how to delete unrelated records.

Delete related records from a database using a delete query

The process of deleting related 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.

  • Change the relationship between the tables involved in the deletion. Specifically, you enable the Referential Integrity and Cascade Delete Related Records options. This allows your deletion to cascade through all affected tables and to remove all related data.

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

  • Convert the select query to a delete query, and then use it to delete the data.

See Create and run a delete query for a full explanation of this process.

Top of Page

Connect with an expert
Contact us
Expand your skills
Explore training

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×