Access delete table records

This Access tutorial explains how to delete table records and delete records that exist in another table in Microsoft Access.

Access delete table records

You can always modify or delete table data manually by viewing the table and then press the delete button in keyboard. With Query, you can delete specific rows of record that meet the criteria. Note that you may delete data in an Access table, but you cannot delete data in linked table. For example, if your Access table is linked to Excel spreadsheet, you cannot modify any data in the spreadsheet from Access.

Access delete table records – Example

Suppose we have created a table called student in Access, which contains 5 student records.

student

Student ID	Student Name
001	Apple
002	Betty
003	Cathy
004	David
005	Elyse

In order to delete the record (the whole row) of Apple, create a new Query, add student table.

Under Design tab, click on Delete button. This will create a Delete Query.

 

Add Student ID to the field, then type “001” in criteria, which is the student ID of Apple.

 

To preview the result of Delete Query (which records will be deleted), click on the View button under Design.

To execute the Query (actually delete the records), click on Run button, then click on Yes to confirm delete row.

 

Now the record of Apple has been deleted.

student

Student ID	Student Name
002	Betty
003	Cathy
004	David
005	Elyse

 

Delete table records that exist in another table or Query

It is an important skill to delete records that exist in another table or Query. For example, we have a student list of 100 persons, we want to remove those student records in the student table. If we do it manually, we have to type all the student ID in the criteria, but it is extremely inefficient.

A better approach is to first prepare an Excel spreadsheet of those 100 student ID, and then create linked table to the spreadsheet, finally create a Delete Query, in  the criteria, select student ID from the linked table.

Example

Suppose we have a Query called DropOut that selects students who have dropped out.

DropOut

Student ID	Student Name
002	Betty
003	Cathy

Now we want to delete the student records in student table where student ID exist in DropOut

Create a Delete Query as below. We are typing a subquery in criteria that selects data from another Query.

 

Click on the Run button, then click on Yes to confirm delete

 

Now the records of Belly and Betty have been deleted.

student

Student ID	Student Name
004	David
005	Elyse

One point to note is that if you type SQL statement directly in SQL View using subquery, you have to type Delete * From student instead of Delete From student in the beginning.