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 Show Table dialog box,
Double-Click EngCourses, Click Close
Step3. To select all the fields, in the EngCourses field list
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
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,
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,
Step8. To return to Design View,
Step9. To run the Delete query,
Step10. To continue the delete process,
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,
Step2. Close the table.
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 Tools Design tab, on the Ribbon, under the Query Tools contextual tab,
Click the Design tab
Step2. To change the query type, on the Design tab, in the Query Type group,
Step3. To select the table you want to append to, on the drop-down list,
Click, Click tblCourses
Step4. To confirm your settings,
Step5. To remove the criteria,
Press & Drag across "Is Null", press: Delete key
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
Step3. To identify a match for the ClassTitle field, in the Append To row:
Click, Click CourseTitle, press: Tab keyTab key
Step4. To identify a match for the Dept field, in that column's Append To row,
Click, Click DeptCode
Step5. To select the ID field in the query design grid, with the black downward-point arrow,
Click the gray selector bar at the top of the ID column
Step6. To delete the field, press:
Step7. To view the records that will be appended,
Step8. To switch to Design View,
Step9. To run the query,
Step10. To append the records,
Step11. To save the query, on the Quick Access toolbar,
Click, type: qappEngCourses Enter
NOTE: The "qapp" is the standard Access naming convention prefix for Append queries.
Step12. Close the query.
Step13. To view tblCourses,
Step14. Close tblCourses.