A DELETE query in Microsoft Access

OK so now you must tread lightly! DELETE queries are destructive — there is no “undo”! The Golden Rule is: back up your table before running this type of query! This is something you would take great care within a database.

The CAT curriculum only covers basic SELECT queries, so I think it is worth experimenting with one or two additional queries to see the power of querying as well as get an idea of how this would work in the “real world”.

Speaking of the “real world”, you would never run a query that deletes (or even just updates) your data without first backing up!

The first step is to right-click copy your table and paste it to make a backup copy of that table. Rename the table — I usually add the letters BKP to the end of the existing name.

  1. Build a simple SELECT query
  2. Based on the table from which you want to delete the records.
  3. Select fields which will make it easy for you to evaluate the records being deleted.
  4. Add criteria as required so that the records you need to delete are the only records in the recordset returned when you run your query. If you add no criteria, naturally all your records will be returned and this would be the way to delete all records from a table.
  5. Run your SELECT query and confirm that the records in the returned recordset are indeed the records you want to delete.

Select the Delete button to convert the query to a DELETE query. Note the new Delete row in the query builder

Read the query as follows: “Delete records from the table where the criteria for the Grade field is 12”. In fact, if you change to the SQL view, you will see the following SQL query:

When you run the DELETE query you will receive a user-friendly notification confirming the number of records that will be deleted. The number of records should correlate to the number of records returned by the SELECT query. There is no “undo”…

Note that if you delete all the records from a table that has an Autonumber field type it will not reset that field’s count to 1. Deleting records from a table and truncating a table are two different things.

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.