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
Danger 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:
No
Undo: You cannot "undo" an action query's action.
Backup first: Before running an action query, be sure you have a current backup of the database or an extra copy of the table that will change.
Verify
selection first: Be sure the query selected the records that you
thought you did! View the datasheet to see
the query's datasheet. Check the records carefully. Then, run the
action query with the Run button
.
The query Append to Scores - Pick A/T in the Language Arts Classes database creates a partial record in the Scores table for each student for the Assignment/Test ID number that the teacher enters. (There must already be a matching record in the Assignment/Test table.)
Problem: The Scores table needs a record for each assignment or test for each of 92 students. How boring and tedious to create all 92 manually!
Solution: Use an append query to create partial records automatically. The teacher (me!) enters an ID number for an 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 or the Scores form and finish the newly appended records by typing in a score for each student. If a student did not have to do the assignment or test, I could delete the score record for that StudentID or mark it to be ignored in the Ignore? field.
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.)
Change the select query to an Append query by clicking the Append Query button. 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 to check what will be appended.
(If you are experimenting with the Language Arts database, enter 90 as the assignment number. This assignment has been added to the table Assignments/Tests already.)
Yes, the query creates a record for each of the 92 students for
assignment ID 90.
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.
About the blank fields: The LetterGrade values were added later with an Update query, which we will look at shortly. With Access 2007, 2010, 2013, and 2016, we can use a Calculated field in the Scores table to get a letter grade. A nice feature!
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 or to all students! 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 easily since they were adjacent to each other. Or a simple Delete query would work, too.
AutoNumbers: The AutoNumber primary key will not reuse the ID numbers of deleted records. That is why the ID numbers for the new records run to 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. I don't know why I
didn't do that at the time.
![]() |
Step-by-Step: Append Query |
![]() |
What you will learn: | to add a record to related table |
Start with: , Projects database from previous lesson 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 Project Staff 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 a new record with the following information:
ProjectName: Celebrate Argentina
ProjectDescription: Designing
tours that display the beauty and history of Argentina
Cost: Delete the default value of 0
Budget: $4000
Be sure to TAB out of the record or click on the Save Record button on the Home ribbon tab.
Save and close the Projects table.
The next step is to enter the staff for the new project in the Project Staff table. You will use an Append query to save some typing but mostly for the practice.
Open the
table Project Staff in Table Design View.
Your query will append records to this table.
So you will need in the append query only two fields: ProjectID and EmployeeID
Close the Project Staff table.
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 because the ProjectID value will be the same for all of
the records that you want to create.
From the Staff table, drag the EmployeeID and Country/Region fields down to the grid.
From the Projects table, drag the ProjectID field to the grid.
Add criteria:
Country/Region = argentina
ProjectID = 8
Since these two tables are not joined, all records in the datasheet will show both 'argentina' and '8'.
View the datasheet.
There are five records out of the original 9. All of these records show ProjectID = 8
and Country/Region = Argentina. Excellent!
Switch back to Design View.
Click the Query Type button and click on Append Query in the Query Tools: Design ribbon tab.
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!
Click the arrow at the right end of the Table Name box and select the table Project Staff.
Click on OK.
The dialog closed and the query opens in Query Design View with a new row, Append To. The values in this new row tell Access how to match fields between the query results and the table to which you are appending records.
Access automatically matches the EmployeeID and ProjectID fields to fields with the same name in the Project Staff table. The field Country/Region does not
have a matching field in the Project Staff table. It is
being used just to select records. Sometimes Access matches fields
incorrectly. Read the grid carefully!
Notice that the grid does NOT show the table to which you will append! When in doubt, make the query a Select query again and change to Append again. The dialog for the table will appear, already filled in.
Run the query.
A message tells you that you will append 5 rows, but does not tell you to which table.
Click on Yes.
The query runs but nothing appears to change.
Save the query as QAppend to Project Staff and close it.
You would not normally need to save this query.
Open the table Project Staff 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.
Complete the records by entering the
following jobs:
Luis P Perez = Project Director
Juanita Gonzales = Asst. Director
Hector Chavez = Events
William Eric Gardner = Tours
Wilhelm G. Heinz = Marketing
The order of employees in the table may be different from the illustration, at least it was between Access 2010 and 2007 for me. Be careful to match the name to the job!
Close the table.