Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Action Queries-Delete Queries

QueryDelete

Delete queries delete all records from the specified table that meet criteria specified in the query.

 

 

Scenario: The table of country names that you have inherited contains some invalid names. You have already updated the records that used to point to these names so they now point to the correct form of the relevant country name. Now you want to remove the invalid names from the table.

 

Before and After

With just a few entries, you could just open the table and delete the bad records. However, if you have many records to delete, a delete query can take care of all of them at once.

IMPORTANT: Before running a query that will modify your data, it is very important to first back up the data. You can do this either by copying the table’s structure and data or by backing up the entire database.

Setting up the query is quite straight forward. Use the table you want to clean up as the query’s record source and include the field that will determine whether or not a record should be deleted.

qdelDeleteCountriesTypos

 

This query will delete records that contain “United States” in the Country field. All other records will be untouched.

If you include additional criteria on the rows below the first criteria, then the  query will delete records that match any one of the criteria.

qdelDeleteCountriesTyposAll

 

 

 

 

 

Just to be safe, however you should not run a delete query before you view it. Notice the distinction between viewing and running the query. ViewDatasheet

 

Viewing a query will display the selected records in the query’s datasheet view.

QueryRun

Running the query, on the other hand, actually deletes the selected records.

 

Note also that double-clicking a query icon in the database window or Navigation Pane, runs the query. If you are not absolutely sure you should be running a query, open it in design view and view it in datasheet view.

Sample files for Action Queries (Access 2000 and 2007/2010 format) can be downloaded from:

 

Comments are closed.