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.

access-delete-table-records-01

 

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

access-delete-table-records-02

 

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.

access-delete-table-records-03

 

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.

access-delete-table-records-04

 

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

access-delete-table-records-05

 

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.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *