Jan's Access Tables & Queries: Delete Query

In the LanguageArtsClasses database an append query was used to add partial records to assist in recording the scores for an assignment. If that query was accidentally run twice, there would be duplicate records. How can we find and remove such duplicate records??

Dialog: New Query - Find Duplicates Query Wizard

Access has a wizard for creating a query that finds duplications. The datasheet will show both sets of records.

Once you have created the Find Duplicates query, a little inspection of the datasheet shows where the duplicate records start. (Score ID 9619 and larger in this case.)
Query Design View: Find Duplicates - with criteria to pick out selected records

Query Design View: Find Duplicates - with criteria to pick out selected records

Now we can add a criteria to pick out those records and turn the query into a Delete query.

When you run the delete query, a message appears, telling you how many records you are going to delete. There should be 92 for this query.

Deletes May Cascade!!

In the LanguageArtsClasses database, the Students and Assignments/Tests tables each have a One-to-Many relationship with the Scores table. This means that one student can have many scores and one assignment or test can have many scores. That makes sense!

Dialog: Edit Relationships - Enforce referntial integrity

When such a relationship is set to "Enforce Referential Integrity", Access will not let you delete a record on the "One" side of the relationship (a student or an assignment) when there are related records on the "Many" side (scores). This is to keep you from having records that refer to other records that do not actually exist! To delete a student or an assignment you have to go to the Scores table and delete all the related scores first, like the example delete query did. Then you will be allowed to delete the student or the assignment.

BUT, if cascading deletes are allowed, you CAN delete a student or an assignment and all of the related scores are also deleted... with no warning! Is this what you want to happen?? Maybe... maybe not! You might need to save those scores somewhere else first.

Is Cascade Delete allowed or not? Check the relationship: Open the Relationships window. Right click on the join line for the relationship you are interested in. The Edit Relationships dialog appears. Look at the check boxes.

No warning! A delete query does not warn you about any cascading changes!

View vs. Run: In Query Design View, the View button

does not run the action for the action query. It just shows the selected records/fields. It does not, however, show what cascading changes will happen. On the other hand, the Run button

actually performs the action. Before running an action query, always view the datasheet to make sure that it selects what you meant for it to select AND check the relationship to see if your deletes or updates will cascade!

Step-by-Step: Delete Query

What you will learn:

to create a delete query

Start with: , Projects database open.

Previously you entered data for a project that has since been canceled. You want to remove that project from the Projects table and the matching staff entries in the table ProjectStaff. You will use a Delete query to do this. This is over-kill for the small tables that you have right now, but we have to practice on something!

You need to know, first, what kind of relationship the tables have. Some relationships will allow cascading deletions and some will not. You don't want to be surprised!

Check the Relationships

Open the Relationships window. (Tools | Relationships...)
Right click on the join line between the tables Projects and ProjectStaff.
From the menu that appears, select Edit Relationship .
The Edit Relationships dialog appears. This relationship is NOT set to enforce referential integrity. Therefore, if you can delete a project that has staff members assigned to it. The deletion will not cascade. It is probably not a good idea to delete records in this kind of relationship!
Check the box to Enforce Referential Integrity. The next two boxes become available to you.
Check the box for Cascade Delete Related Records.
Click on OK to close the dialog.

Cannot save changes to the relationship:

If one of the tables involved in the relationship is open or if something that uses that table is open (a query, form or report), you cannot edit the relationship.

Solution: Cancel out of the Edit Relationships dialog. Close all objects that involve the tables in the relationships. Then try again.
Close the Relationships window.

Check the Table and Its Related Records

Open the table Projects.
Click on the + at the left of the project "Conquer Your Computer".
A subdatasheet opens that shows what staff members are assigned to this project.

Leave this table open while you work on the query.

Create Select Query

Suppose you are going to delete a project, but you have forgotten what its name was. It had something to do with computers. The Like operator can help you find what you want. (Yes, you just saw the right name, but we are practicing here!)

Create a new query based on the Projects table. Include the fields ProjectID and ProjectName.
You need to select the project that had to do with computers. For practice, you will use the Criteria row to look for the name, as though you did not know exactly what the project's name was. So helpful!
Under ProjectName add in the Criteria row the following:
Like "*computer*".
The asterisk * allows any kind or number of characters. So this criterion matches text that contains "computer" somewhere in it.
View the datasheet for the query.
Only one project contained the text "computer". If there had been others, you would need to figure out which one was the project that you wanted and set new criteria to select it or them.

Change Query to a Delete Query:

Switch to Design view.
From the Query Type button, select Delete Query.
The title bar changes and a Delete row appears in the grid.

Notice that this command does not have the three dots after like like the Make-Table and Append Queries did. There will not be a dialog for this one, but you will get a warning message.
Save the query as Delete project
Run the query.
A message appears telling you how many rows will be deleted.
The message does not mention which table will be affected! You must know your query!!!
Click on Yes.
The message disappears and you are back in the design grid. Did anything happen?? Of course it did!

Verify Deletion

Switch to the window for the Projects table, which you left open earlier.
It shows #Deleted in all of the cells for the project you just deleted.
Click on the + at the left of the deleted record to open the subdatasheet. There are no records. Since Cascade Delete was enabled, the matching records in the ProjectStaff table were deleted without any comment!
Close the Projects table and open it again.
No more #Deleted!

The only way to know that a record was deleted is to look at the ProjectID field values. Since those are AutoNumbers, if any numbers are skipped, you know that those records were deleted.
Close the query and table.

Tables & Queries:Delete Query

Example: Delete Query