Tables & Queries:
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016
A 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.
Danger 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.
Project 3: Tables & Queries
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??
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.)
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!
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!
Have we said this enough yet?...
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
Check the Table and Its Related Records
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!)
Change Query to a Delete Query:
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~