- Once you delete records using a delete query, you can't undo the operation. Therefore, you should preview the data that the query selected for deletion before you run the query. You can do this by clicking View
on the toolbar, and viewing the query in Datasheet view. - You should maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.
- In some cases, running a delete query might delete records in related tables, even if they're not included in the query. This can happen when your query contains only the table that's on the "one" side of a one-to-many relationship, and you've enabled cascading deletes for that relationship. When you delete records from the "one" table, you'll also delete records from the "many" table.
- When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the query’s UniqueRecords property must be set to Yes.
Delete records by using a query that includes only the "one" table in a one-to-many relationship
- Create a new query that contains the "one" table from which you want to delete records.
- In the Database window, click Queries
under Objects, and then click New on the Database window toolbar. - In the New Query dialog box, click Design View, and then click OK.
- In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
- Double-click the name of each object you want to add to the query, and then click Close.
- Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
- To view the query's results, click View
on the toolbar.
- In the Database window, click Queries
- In query Design view, click the arrow next to Query Type
on the toolbar, and then click Delete Query. - Drag the asterisk (*) from the field list for the table to the query design grid.
From appears in the Delete cell under this field, as shown in the illustration that follows.
- To specify criteria for deleting records, drag to the design grid the fields on which you want to set criteria.
Where appears in the Delete cell under this field, as shown in the following illustration.
- In the Criteria cell for the fields that you have dragged to the grid, type the criteria.
- To preview the records that will be deleted, click View
on the toolbar. To return to query Design view, click View
on the toolbar again. Make any changes you want in Design view. - Click Run
on the toolbar to delete the records.
Delete records by using a query that includes both tables in a one-to-many relationship
- Create a new query that contains the tables from which you want to delete records.
- In the Database window, click Queries
under Objects, and then click New on the Database window toolbar.
- In the New Query dialog box, click Design View, and then click OK.
- In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
- Double-click the name of each object you want to add to the query, and then click Close.
- Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
- To view the query's results, click View
on the toolbar.
- In the Database window, click Queries
- In query Design view, click the arrow next to Query Type
on the toolbar, and then click Delete Query.
- From the table with related records (on the "many" side of the one-to-many relationship), drag the asterisk (*) from the field list to the query design grid.
From appears in the Delete cell under this field, as shown in the illustration that follows.
- To specify criteria for deleting records, drag the fields on which you want to set criteria from the primary table (on the "one" side of the one-to-many relationship) to the design grid.
Where appears in the Delete cell under this field, as shown in the following illustration.
- In the Criteria cell for the fields that you have dragged to the grid, type the criteria.
- To preview the records that will be deleted, click View
on the toolbar. To return to query Design view, click View
on the toolbar again. Make any changes you want in Design view.
- Click Run
on the toolbar to delete the records from the table on the "many" side.
- Select the field list for each table on the "many" side and press DELETE to remove it from the query.
- With just the primary table remaining in the query and the field on which you want to set criteria in the design grid, click Run
again.
Microsoft Access deletes the specified records from the "one" table.
Delete records from a single table or multiple tables that have a one-to-one relationship
- Create a new query that contains the tables from which you want to delete records.
- In the Database window, click Queries
under Objects, and then click New on the Database window toolbar.
- In the New Query dialog box, click Design View, and then click OK.
- In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
- Double-click the name of each object you want to add to the query, and then click Close.
- Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
- To view the query's results, click View
on the toolbar.
- In the Database window, click Queries
- In query Design view, click the arrow next to Query Type
on the toolbar, and then click Delete Query.
- For the tables you want to delete records from, drag the asterisk (*) from the field list to the query design grid.
From appears in the Delete cell under these fields, as shown in the illustration that follows.
- To specify criteria for deleting records, drag to the design grid the fields on which you want to set criteria.
Where appears in the Delete cell under these fields, as shown in the following illustration.
- In the Criteria cell for the fields that you have dragged to the grid, type the criteria.
- To preview the records that will be deleted, click View
on the toolbar. To return to query Design view, click View
on the toolbar again. Make any changes you want in Design view.
- Click Run
on the toolbar to delete the records.