Example: Append Query

The query Append to Scores - Pick A/T in the LanguageArtsClasses database creates a partial record in the Scores table for each student for the Assignment/Test ID number that you enter. (There must already be a matching record in the Assignment/Test table.)

Problem: Each assignment or test needed a record in Scores for each of the 92 students. Boring to create all 92 manually!

Solution: Use a query to create partial records automatically. The teacher (me!) enters an ID number for the assignment or test. Running the query adds a record to Scores for each student for the selected assignment or test. There are no scores yet. After running this append query, I could then open the Scores table and finish the newly appended records by typing in a score for each student.

Steps in the process:

• Create a Select query by selecting the source tables/queries and the fields that you want to append to the destination table.

• Apply any sorting and criteria (This one includes a parameter for the ID number for the assignment.)

• Change the select query to an Append query by clicking the Append button on the
  Query Tools: Design ribbon tab. A dialog asks for the name of the table to which you will append records. A new row, Append To, appears in the Design grid.
  

• Match fields: Each column that shows in the query results must have a matching field in the destination table. You might use some fields to sort or select records that do not themselves show in the results.

  Make sure that the field in the Append To row is the one that matches the field at the top of that column. Access is pretty smart but sometimes it guesses wrong about this if the field names are not exactly the same.
  

• View the datasheet for assignment #89 to check what will be appended.
  Yes, there is a record for each of the 92 students for assignment ID 89.

• Run the query by clicking the Run button.
  
  

• Dialog: Tells how many rows will be appended. Very helpful to know in many situations.
   

• View results: The table with the newly appended records does not open automatically. You must open the destination table yourself to view the appended records.

  The purpose for this query is to create records in the Scores table that the teacher can then use to enter the scores without having to type in the ID values for all 92 students. It worked great for that!

About the blank fields: The LetterGrade values are added later with an Update query, which we will look at shortly. The Ignore? field is a Yes/No field that would normally be blank. A Yes in this field would mean that the score would not be included in averages.

Point of Confusion = Disadvantage: Unnecessary records are created by this append query if the assignment or test was not actually assigned to all classes! A/T ID 87 is an example of this. Assignment 87 was a spelling test that was not assigned to class D for scheduling reasons at the end of the year. The C/S ID value of 8 is the code for class D and Spelling. So, the records for assignment 87 and C/S ID value of 8 have no score.

The unwanted records could be deleted manually or by using a Delete query.

AutoNumbers: The AutoNumber primary key will not reuse the ID numbers of deleted records. That is why the ID numbers for the new records are over 9000 when there are only 7537 records in the table. Quite a number of records were deleted along the way.

If there were going to be a lot of assignments that would not be given to all three classes, the query could be changed to include a parameter for the C/S field, which picks out the class and subject of the assignment. Wonder why I didn't do that?? Got smart just a bit too late!