databasedev.co.uk - database solutions and downloads for microsoft access

Creating a Delete Query in Microsoft Access:

A Microsoft Access delete query deletes records from a single database table or database tables. Of all of the different action queries available in Microsoft Access (Append Queries, Update Queries, Make-Table Queries and Delete Queries) the delete query is one of the most dangerous. Unlike the others mentioned, the Microsoft Access delete query will remove records from your tables permanently and forever.

As with the other types of action queries, the delete query will work with a group of records that meet a specified criteria that you apply. You can use the delete query to remove all records or only records that meet the defined criteria.

If you wish to use the delete query to remove records from multiple tables rather than just a single database table, you will need to ensure that:

• You have relationships defined between related tables in your Microsoft Access database
• You have enforced the Referential Integrity for the relationships between your chosen tables.
• You have opted to Cascade Delete Related Records for the relationship type

If you are using the delete query to remove records from multiple tables that are related in a One-To-Many relationship without having defined the option to Cascade Delete Related records, Microsoft Access will only delete the records from one table at a time. If this is the case, you must delete the records from the many side of the relationship first (to ensure against orphaning records), and then delete the records from the one side of the relationship.

Note: Due to the permanent effects of working with a delete action query, you should always make a backup copy of your tables, or your database before attempting this option.

How to create a Microsoft Access Delete Query:

1. Create a SELECT query to determine the records that will be deleted. Apply any required query criteria
2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Delete Query
3. If needed, modify the query further so that the correct fields will be deleted with the desired new data
4. Click on the Run (!) button to run the action query
5. When informed of the number of records to be deleted in the Microsoft Access dialog box, click Yes
6. Close the query, saving if required

Now consider the following scenario example, that will illustrate the use of a Microsoft Access delete query:

Your company runs training courses, with students signing up for these courses. Student membership lasts for 2 years. All of this information relating to their details is stored in your StudentInformation table. You would like to remove any records from this table that are not of current students, so you will archive records by initially appending the data from your StudentInformation table into another table (ExpiredStudents) and then deleting these records from the StudentInformation table.

If you have read the previous article that detailed How to Append records using a Microsoft Access Append Query you should be aware of the following:

The original StudentInformation table, containing 52 records, would look like the following:

As you will see, the above table contains contact information relating to the student members. You will see from the fields included, that there is a field detailing when the Student Enrolled on the course (dtmEnrolled). This field also is displaying records older than two years old.

In our scenario, we only wish to keep records in the table where the student is still currently active. With the courses being a two year duration, we only want records that are greater than or equal to today's date minus two years.

To allow us to append records to an archive table, we must first have the archive table created, whether it be in the active database or in another database. The table should ideally have exactly the same structure as the original table and contain the same field names and data type/sizes.

In our example we have an archived table created, named tblExpiredStudents, which will hold the historical records of students whose two years have expired.

After having appended these records to the archive table, named tblExpiredStudents as detailed in the How To Append records using a Microsoft Access Append Query we now need to create a Delete Query to remove these records from the original table named tblStudentInformation:

You will see that we have added a criteria to the dtmEnrolled field, as specified we want to delete any records that are older than two years from today's date. The criteria that is applied is:

<=DateAdd("yyyy",-2,Date())

This will display only records that are older than 2 years from today's date.

Before we change the query type to an Delete query, we should check the results that this displays. We can do this by clicking on the Datasheet button on the toolbar.

If we check the datasheet displaying the results of running the query with the DateAdd criteria applied, we will now see that the record count is displaying only 20 records. This is showing 20 records that need to be deleted due to them being greater than 2 years old:

As we have now copied (appended) the data to the archive table, we can now remove (delete) the data from the main table. To do this we need to go back into the Microsoft Access query design view.

Once in design view, we change the query type using the Query Type button on the toolbar. From the query type drop-down list, change the query type to a Delete Query.

You will now see the the query design includes a new row labeled Delete, which allows you to specify a criteria to which the data will be deleted. You should ensure that you specify a WHERE condition here, or you will end up deleting all records from the table. You will see below this row in the query design, that includes the WHERE criteria which will delete all records older than 2 years:

We now need to Run this query, using the Run button to delete the data from the original database table that matches the applied DateAdd criteria. The warning dialog box indicates the number of records that will be deleted, click Yes to accept this:

This will now have deleted all records that matched the applied criteria from the original database table.

Remember that the delete query will permanently remove records from the specified table(s), therefore it is very important that you have either appended your data to an archive table, or have backed up the table(s) or database.

Check Your Delete Query Results

Once you have ran the delete query, you can check the results by once again viewing the datasheet results. If the delete action has ran correctly, there will now be no records visible in the datasheet.