Access: Create delete query - how it works
With a delete query, data records that are no longer required can be quickly deleted in Access. In this practical tip, we have put together for you how to create a deletion query and what you should consider.
What you should know before using a delete query
- A delete query in Access always removes an entire data record. If you only want to delete individual fields of a table, use the update query.
- Once deleted, data cannot be restored. It is best to make a backup copy of the table.
- The easiest way to do this is via the clipboard. Use the "Copy" and "Paste" commands from the context menu to create a copy of the corresponding table.
- With the delete query you also remove the key value.
How to create a delete query in Access
- Navigate to the Create tab, then click Query Design in the Queries group.
- The "Show table" dialog box now appears. Double-click on the table from which you want to delete data records and confirm your selection with "Close".
- Double-click the asterisk (*) to add all table fields to the query or select individual fields by double-clicking.
- Then select the "Delete" entry in the "Query type" group.
- You must now specify criteria so that only the data records that are really to be deleted are removed. Otherwise, the delete query removes all the records in the table.
- You create the syntax of the query individually according to your needs. You can use the Expression Editor to help you create it. You can open it by clicking on "Generator" in the "Query Setup" group.
- For example, if you want to delete all records that do not contain a phone number, select the "Phone number" field and enter "Is zero" in the criteria.
- The procedure presented here works from Access 2010.