Using Delete Queries

It is generally a bad practice to delete data. Once the data is gone, it may not be able to be recovered.

Let's say a student had graduated and left the University of the Midwest. If we deleted that student and referential integrity wasn't enforced, we would then have orphan records for that student's enrollments in tblEnrollments, and we would only have a student ID with no other information about that student in our database.

On the other hand, if referential integrity was enforced, to delete the student, we would need to turn on Cascade Delete Related Records for the relationship between tblStudents and tblEnrollments. If we deleted the student at this point, we would remove both the student record and the records of any courses the student enrolled in, including their grades. This would delete important historical enrollment and grade information.

In either case, if the student wanted a transcript of their course at the university at some time in the future, it would be very difficult, if not impossible, to provide this information to the student.

An alternative to deleting data is to include a Yes/No field indicating whether the record is active or inactive. This means that the data is still available for us to refer back to. The one real downside to having an active field is that we need to remember to take this field into account when writing queries.

Running a Delete Query

In the table we just created we don't have to worry about relationships as it isn't yet related to other tables. It also is one of those rare instances where using a Delete query makes sense. A Delete query removes entire records from the database. We can delete the data we imported because it's new, and we don't want the records we are removing to ever become integrated into our database. Let's use a Delete query to remove the stray comment rows.

Step1. To start creating a new query, on the Ribbon,

Click the Create tab, Click 

Step2. To add the EngCourses table to the design, in the Add Tables pane,

Double-Click EngCourses

Step3. To select all the fields, in the EngCourses field list,

Double-Click 

All the fields are selected.

Step4. To place all the fields on the query design grid, with all the fields selected,

Press & Drag any field to the query design grid

All fields are displayed on the query design grid.

NOTE: If you Double-Click the asterisk, all fields will display in the query dataset, but the fields won't show up individually on the query design grid.

Step5. To change the query type, on the Design tab, in the Query Type group,

Click 

The grid now displays a Delete row. The query design window should look like this:

We want to delete any of the rows that are just comments. The English departments practice was to always put comments in the ClassTitle column. If there is only a value in ClassTitle, then the row only contains a comment. So we can check to see if there is a null value in the ClassCode field to eliminate the notes.

Step6. To add the Criteria to select the notes, in the ClassCode field,

Click the Criteria cell, type: Is Null

Step7. To view the records to be deleted,

Click 

We see the 7 records of the notes in the query results. This verifies that our query is only pulling the records we want to get rid of.

Step8. To return to Design View,

Click 

Step9. To run the Delete query,

Click 

We see an alert warning us we are about to delete 7 records and giving us the opportunity to confirm that intention:

Step10. To continue the delete process,

Click 

At this point, we could save the Delete query, but it is unlikely that we will ever rerun this same query. However, we will use what we have with modifications to append the remaining data.

First, let's confirm that the data has been deleted.

Confirming the Delete

While Access showed us a message confirming the deletion, we want to be sure that the data we expected was deleted. Let's take a look at EngCourses to be sure the rows were deleted.

Step1. To open the EngCourses table,

Double-Click EngCourses

There are 50 records in the table. Remember, when we looked at the data after we imported this table, there were originally 57 records. The current number of records confirms that 7 records were deleted.

Step2. Close the table.

You see the query design grid.

Running An Append Query

Now that we have only the data we want in the EngCourses table, we can change our query to an Append query to allow us to add the remaining records to tblCourses.

One thing we need to deal with when appending data from one table to another is the differences in how the tables are structured. Before we try to append the records from EngCourses to tblCourses, we need to study the structure of the two tables. Are their fields compatible?

These images compare fields in both tables:

We can readily identify some potential roadblocks to the append Action. The table EngCourses has:

• Field names that do not match the names in tblCourses
• The field ID that does not exist in tblCourses

As the graphic shows, the two tables that will be used for the Append query do not have matching fields. Let's proceed and see how Access helps us to deal with these issues using the Append query.

Step1. To switch to the Query Design tab, on the Ribbon,

Click the Query Design tab

Step2. To change the query type, on the Design tab, in the Query Type group,

Click 

A dialog box appears asking which table it should append the selected records to. Notice the radio buttons that would allow us to append to a table in a different database if preferred. However, we want to append these records to tblCourses in our current database, so we will keep the default choice.

Step3. To select the table you want to append to, on the drop-down list,

Click , Click tblCourses

Step4. To confirm your settings,

Click

The dialog box closes.

We need to remove the criteria we used for the Delete query.

Step5. To remove the criteria,

Press & Drag across "Is Null", press: Delete key

Now we can deal with the difference in the structure of our tables.

Matching Fields to Append

Notice the Append To row that has been added to the query design grid. Where Access recognizes a match between field names, the destination field name is filled in. In the other fields, ID, ClassCode, ClassTitle, and Dept, Access left the Append To row blank. It needs help to identify the destination field.

Step1. To start identifying the destination field for the ClassCode column,

Click the "Append To" field in the ClassCode column

Step2. To select the matching field,

Click , Click CourseCode Tab key

We have now added the missing link for the CourseCode field of the append.

Step3. To identify a match for the ClassTitle field, in the Append To row:

Click , Click CourseTitle, press: Tab key Tab key

Step4. To identify a match for the Dept field, in that column's Append To row,

Click , Click DeptCode

The ID field is a field that exists only in EngCourses. We cannot append a field to a table if that field does not exist in the destination table.

At this point we have two choices. We could go to the design of the tblCourses and add an ID field. Or we could simply choose not to append the field from EngCourses to tblCourses. Since this is the autonumber field Access automatically added and it is not needed for our data, we will delete the ID field from this append.

Step5. To select the ID field in the query design grid, with the black downward-pointing arrow displaying,

Click the gray selector bar at the top of the ID column

Step6. To delete the field, press:

Delete key

We can continue to preview and then run the query.

Step7. To view the records that will be appended,

Click 

There are 50 records of courses which will be appended to tblCourses when the query runs. Let's return to Design View and run the Action query.

Step8. To switch to Design View,

Click 

Step9. To run the query,

Click 

You see an Access alert box telling you that you are about to append 50 rows of data to the tblCourses table:

Remember that you cannot Undo the append once you have confirmed it!

Step10. To append the records,

Click 

The records are added to tblCourses. It is more likely that an Append query would be rerun as there are workflows where someone puts data into an Excel file and then it is appended to an Access database at set time periods. Since this is the case, we will save this query.

Step11. To save the query, on the Quick Access toolbar,

Click , type: qappEngCourses Enter key

NOTE: The "qapp" is the standard Access naming convention prefix for Append queries.

Step12. Close the query.

Let's look at tblCourses to see how the record count has changed.

Step13. To view tblCourses,

Double-Click tblCourses

As a result of the append, our table now has 149 courses instead of 99.

Step14. Close tblCourses.