Tables & Queries:
Delete Query

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

A Delete query Button: Delete Query always deletes entire records from a table, not just the fields and values that show in the query datasheet. The query can include fields from many tables and queries to select the records to delete, but records are deleted from only one table, at least to start with.

WarningDanger with a delete query: A delete query may affect more tables and records than you expect, depending on what relationships the table is in.  See the discussion below about cascading deletes. You cannot Undo a deletion, or any other action query's actions.

Once again:

WarningNo Undo: You cannot "undo" an action query's action.

TipVerify selection first: Be sure you have selected the records that you thought you did! Use the View button Button: Views - Datasheet (2003) to see the query's datasheet. Check the records carefully.  Then run the action query with the Run button Button: Run (2003).

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


Example: 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 WizardAccess 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 recordsNow 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.

Remember... You cannot Undo a delete query's action.

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!

Relationships: LanguageArtsClasses database

Dialog: Edit Relationships - Enforce referntial integrityWhen 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.

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

TipView vs. Run: In Query Design View, the View button Button: Views - datasheet (2003) 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 Button: Run 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!

Have we said this enough yet?...

WarningNo Undo: You cannot "undo" an action query's action.

TipVerify selection first: Be sure you have selected the records that you thought you did! Use the View button Button: Views - datasheet (2003) to see the query's datasheet. Check the records carefully.  Then run the action query with the Run button Button: Run (2003).

Icon: Step-by-Step 

Step-by-Step: Delete Query

 Icon: Step-by-Step

What you will learn:

to create a delete query

Start with:  Class disk, 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

  1. Relationships with context menuOpen the Relationships window. (Tools | Relationships...)

  2. Right click on the join line between the tables Projects and ProjectStaff.

  3. 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!

  4. Dialog: Edit Relationships - Projects and ProjectStaffCheck the box to Enforce Referential Integrity. The next two boxes become available to you.

  5. Check the box for Cascade Delete Related Records.

  6. Click on OK to close the dialog.

    Icon: ProblemCannot save changes to the relationship:
    Message: The database engine could not lock the table 'Projects' because it is already in use by another person or process.
    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.

  7. Close the Relationships window.

Check the Table and Its Related Records

  1. Open the table Projects.

  2. 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.

    Datasheet with subdatasheet

    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!)

  1. Query Design View: select ProjectName=Like"*computer*"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!

  2. 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.

  3. Query Datasheet View: Projects table, ProjectName= Like "computer"Icon: Datasheet 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:

  1. Query Design View: Delete queryIcon: Design Switch to Design view.

  2. Button: Query Type - Delete QueryFrom 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.

  3. Icon: Save Save the query as Delete project

  4. Message: You are about to delete 1 row(s) from the specified table.Icon: Run Run the query.
     A message appears telling you how many rows will be deleted.
    WarningThe message does not mention which table will be affected! You must know your query!!!

  5. Click on Yes.
    The message disappears and you are back in the design grid. Did anything happen?? Of course it did!

Verify Deletion

  1. After delete query has run while records were open, fields show #Deleted.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.

  2. 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!

  3. After closing and re-opening the table, deleted records goneClose 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.

  4. Close the query and table.