Section 7.3. Append Queries | Access 2007 for Starters[c] The Missing Manual

7.3. Append Queries

An append query selects records from a table, and then inserts them into another table. (Technically speaking, appending is the process of adding records to the end of a table.)

You may create an append query for a number of reasons, but usually you do it to transfer records from one table to another. You may want to use an append query to transfer records from one database to another. This trick's handy if you have duplicate tables in different databases (perhaps because different people need to use the database on different computers).

Note: Once you've finished copying records to a new table, you may want to follow up with a delete query (Section 7.4) to remove the old versions.

Append queries also make sense if you're working with a super-sensitive database. In this case, you might enter new records in a temporary table so someone else can look them over. When the inspection's finished, you can then use an append query to transfer the records to the real table.

Append queries are a bit stricter than other types of action queries. In order to transfer the records, you need to make sure the two tables line up. Here are some guidelines:

Data types must match . The fields you select (from the source table) and the fields you're heading towards (in the target table) must have matching data types. However, the names don't need to match. You can configure your query so that information drawn from a field named FirstName is placed into a field name F_Name, provided they're both text fields.
You can ignore some fields . If the source has fields that aren't in the destination table, just don't include them in your query. If the destination table has fields that aren't in the source, then Access leaves them blank, or uses the default values (Section 4.1.2). However, if you leave out a required field (one that has the Required field property set to Yes, as explained in Section 4.1), then you'll get an error.
Access enforces all the normal rules for adding a record . You can't do things like insert data that violates a validation rule (Section 4.3), and you can't insert duplicate values into a field that has a primary key or a unique index (Section 4.1.3).
If the destination table has an AutoNumber field, then don't supply a value for that field . Access automatically generates one for each record you insert.

Note: You can't copy AutoNumber values in an append query. If you use AutoNumber fields for your ID fields, then the new copied records have different ID numbers from the originals .

Access gives you another choice that's similar to the append query: the make-table query , which is the same in all ways but one. The make-table query creates the destination table, and then copies the records to it.

7.3.1. Creating an Append (or Make-Table) Query

The following steps show you how to create an append or make-table query. You'll transfer records from the Contacts table in the Marketing.accdb database to the PotentialClients table in the Sales.accdb database. (You can find both these databases on the "Missing CD" page at www.missingmanuals.com.)

Open the source database .

In this example, that's the Marketing.accdb database that has the contact information.
Create a new query by choosing Create Other Query Design .

The Show Table dialog box appears.
Using the Show Table dialog box, add the source table that has the records you want to copy. Then click Close to close it .

This example uses the Contacts table.
Change your query to an append query by choosing Query Tools Design Query Type Append (or choose Query Tools Design Query Type Make Table to convert it to a make-table query) .

The destination table (the PotentialClients table in the Sales.accdb database) already exists. For that reason, you use an append query instead of a make-table query.

When you change your query to an append or make-table query, Access asks you to supply the destination table (the place where you'll copy the records), as shown in Figure 7-6.

If you want to transfer the records to another database, then choose Another Database, and then click Browse. Browse to your database file, and then click OK to select it .

You're transferring records to the Sales.accdb database.

Especially if you plan to keep using this new query, be sure to keep the destination database in the same spot. If you move the destination file to another location (or rename it), Access can't find it when you run the query and gives you an error.

Figure 7-6. Access wants to know where you plan to transfer the records you're copying. You can choose a table from the handy drop-down list. If you're copying data from one database to another, then choose the Another Database option, click the Browse button to specify the database file, and then click OK.

In the Table Name box, enter the name of the table to which you want to transfer the records .

If you're creating an append query, then the table you choose must already exist somewhereeither in the database file or another one you have on hand. You can pick it out of the Table Name drop-down list.

If you're creating a make-table query, then you need to type in the name for a brand-new table. Access will create this table when you run the query.

Here, you're transferring records to the PotentialClients table.
Click OK to close the Append or Make Table dialog box .
Now, add the field (or fields) you want to copy from the source table .

Remember, you don't have to copy all the fields. In this example, all you need is the FirstName and LastName fields.

If you're creating an append query, then fill in the names of the destination fields in the Append To boxes .

In this example, set the Append To box for the FirstName field to F_Name. That way, Access copies the information from the FirstName field in the source table to the F_Name field in the destination table (Figure 7-7). Similarly, set LastName so it appends to L_Name.

Figure 7-7. This append query transfers the information from the Contacts table in the Marketing database to the PotentialClients list in the Sales database. Since both these tables use ID fields with the AutoNumber data type, the ID numbers in the copied records will be different from the ID numbers in the original records. (If this isn't the behavior you want, then you need to copy the AutoNumber ID from the Contacts table to a normal numeric column in the PotentialClients tableone that doesn't use the AutoNumber feature.)

If you want to copy only some of the records in the source table, then set the filter conditions you need .

Like everywhere else in Access, these filters determine what records are copied from the source table. To set a filter condition, just fill in the Criteria box for the appropriate field.

If you add a filtering field to an append query, but don't want to copy the field's value to the target table, then don't put anything in its Append To box.

If you add a filtering field to a make-table query, but don't want to copy the field's value to the target table, then turn off that field's Show checkbox.
Right-click the tab title, and then choose Datasheet View to see the rows that your query affects .

This step lets you preview the rows you're about to copy.
If you're confident you've got things right, then switch back to Design view, and then choose Query Tools Design Results Run to transfer your records .

Access warns you about the change it's about to make. Click Yes to copy the records. Access doesn't show you the copiesyou need to track those down by browsing the destination table's datasheet.

At this point, you have the same records in two placesthe source table and the destination table. You may want to follow up with a delete query to clean out the original versions, as described in the next section.
If you want to save your query, hit Ctrl+S (or close the query tab). You need to supply a name for your query .

If you don't plan to use your query again, then consider deleting it.