Tables & Queries:
Append Query

Title: Jan's Illustrated Computer Literacy 101


An Append query Button: Append Query puts a copy of the records and fields that you select into an existing table.

What good is an append query?

  • To add partial records which share the same data to a table. This could save a lot of selecting and typing and ensures that you create the correct number of similar records. (The example below is this type.) Of course some data will have to be entered separately for each record.

  • The start of a two-step process to move records:

    • Append records to a different table

    • Delete the records from the original table

  • As part of a background process that uses a temporary table in a complex procedure.

WarningDanger with an append query - Nothing will stop you or warn you about running the query again and appending another copy of the same records.  Access assumes that you knew what you were doing! The exception is an append query that includes the primary key for the table. Access will not let you repeat values for a primary key.

It bears repeating:

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
    Summary
    Quiz
    ExercisesTo subtopics

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



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:

  • Query Design View: Append to Scores - pick A/TCreate 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.)

  • Change the select query to an Append query. 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.
     

  • Query Datasheet View: Append to Scores - pick A/TView the datasheet to check what will be appended.
    Yes, there is a record for each of the 92 students for assignment ID 88.
     

  • Run the query by clicking the Run button.
     

  • Message: You are about to append 92 row(s).Dialog: Tells how many rows will be appended. Very helpful to know in many situations.
     

  • Table Datasheet View: Scores table, after appending records for assignment ID 88View results: The table with the newly appended records does not open automatically. You must open the destination table yourself to view the appended records.
     

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.

Icon: ConfusionPoint 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. The C/S value of 8 is the code for class D and Spelling. So, the records for assignment 87 and C/S value of 8 have no scores.

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??
 


Icon: Step-by-Step 

Step-by-Step: Append Query

 Icon: Step-by-Step

What you will learn:

to create an append query

Start with:  Class disk, Projects database open.

An append query adds new records to an existing table by copying data from other tables or queries. One use for this kind of query is to create partial records to be sure that the correct number of records are created.

You will add a new project to the Projects table. You want to assign all staff members in Argentina to the new project. You will use a query to select those staff members and append them to the ProjectStaff table. Since they will not all have the same job for the project, you will have to enter the data for the Job field separately.

Add Record to Projects Table

  1. Open the Projects table from the Database Window.
     

  2. Add a new record with the following information:
    ProjectName:              Celebrate Argentina
    ProjectDescription:     Designing tours that display the beauty and history of Argentina
    Cost:                                leave at the default value of 0
    Budget:                          $4000
    Table Datasheet View: Projects - with new record
     

  3. Close the Projects table.


What fields need to be in the append query?

  1. Table Design View: ProjectStaffOpen the table ProjectStaff in Table Design View.
    Your query will append records to this table.
     

  2. Inspect the table: What fields should your append query include? 
     
    Only the fields ProjectID and EmployeeID.
    ProjectStaffID is an AutoNumber field, so you do not want to enter values for it. For the Job field you will have to enter the values yourself since they will not be the same for everyone.
     

  3. Close the ProjectStaff table.


Create Select Query

  1. Create a new query based on the Staff and Projects tables.
    These tables do not have a relationship but for this query you can use fields from both. The ProjectID value will be the same for all of the records that you want to create.
     

  2. From the Staff table, drag the EmployeeID and Country/Region fields down to the grid.
     

  3. From the Projects table, drag the ProjectID field to the grid.
     

  4. Query Design View: select records for append queryAdd criteria:
    Country/Region = argentina
    ProjectID = 8
     
     

  5. Query Datasheet View: appending staff to ProjectStaffIcon: Datasheet View the datasheet. All records show ProjectID = 8 and Country/Region = Argentina. Excellent!
     

  6. Icon: Design Switch back to Design View.
     


Change to Append Query

  1. Button: Query Type - AppendClick the Query Type button and select Append Query....
    A dialog appears for you to choose the table to which you want to append the records. It must be one in the list but it can be in a different database!
     

  2. Dialog: AppendSelect the table ProjectStaff .

    Query Design View: appending staff to ProjectStaffAccess matches the EmployeeID and ProjectID fields to those in the ProjectStaff table. The field Country/Region does not have a matching field in the ProjectStaff table. It is being used just to select records. Sometimes Access matches fields incorrectly. Read the grid carefully!

     

  3. Icon: Run Run the query.

     

  4. Icon: Save Save the query as Append to ProjectStaff.
     


View Results and Complete Records by Entering Values for Job

  1. Table Datasheet View: ProjectStaff - after appending recordsIcon: Datasheet Open the table ProjectStaff in datasheet view.
    There are new records for the staff for project #8.

    The EmployeeID field is showing a name instead of a number. Do you remember why?
     
    In the design of this table, you made EmployeeID a Lookup field based on the query QStaff-FullName. The field contains an ID number, but displays the full name for your convenience.
     
    It is easier to remember employees by their names instead of their ID numbers! If only the ID number showed, it would be easy to make a mistake when you start typing in jobs.
     

  2. Table Datasheet View: ProjectStaffComplete the records by entering the following jobs:
    Luis P Perez = Project Director
    Hector Chavez  = Events
    William Eric Gardner = Tours
    Wilhem G. Heinz = Marketing
    Juanita Gonzales = Asst. Director
     

  3. Close the table.