Microsoft Office Tutorials and References
In Depth Information
Moving Data from One Table to Another with Append Queries
9. Click the Run button to create the new table.
Access asks whether you’re sure, because you won’t be able to undo
10. Click Yes to create the new table.
Access quietly creates the new table.
11. Check the new and old tables to make sure that you got what you
need in the new table.
You may want to edit the table design because the new table doesn’t
inherit the field properties or the primary key setting from the original
table. (See Book II, Chapter 1 for more information on table design.)
Moving Data from One Table to
Another with Append Queries
An append query copies data from one or more tables or queries in your
database and adds the data selected by the query to an existing table as new
records. As with other queries, you can use criteria to tell Access exactly
which data to append.
Append queries are useful for archiving information, moving data between
databases, and performing other useful housecleaning chores.
Cutting and pasting may be an easier way to append records from one table
to another if you’re appending only a few records. See Book II, Chapter 4 for
Access gets a little picky about data that you append with an append query,
especially primary key field data. You must follow these rules when
appending records to another table:
✦ Data that you want to append must have unique values in the primary
key field. Each value in the primary key field must be unique in the table
to which the data is being added, because by definition, no value can
repeat in a primary key field. If the field is blank, or if the same value
already exists in the table, Access doesn’t append the records.
✦ If an AutoNumber field is in the table to which the data is being
appended, don’t append data in that field. Access automatically
generates new numbers in the AutoNumber field for the new records;
AutoNumber values previously generated can’t be appended.
✦ The data type of each field you’re appending must match the data
type of fields in the table to which they’re being added.