Section 8.3. Append Queries | Access 2007[c] The Missing Manual

8.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 8.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.3), 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 5.2.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.

8.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 8-6.

Figure 8-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.

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.
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 8-7). Similarly, set LastName so it appends to L_Name.

Figure 8-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 Section 8.4.
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.

8.3.2. Getting AutoNumbers to Start at Values Other Than 1

Access gurus also use append queries in one of the most tricky Access workarounds: changing a table so its AutoNumber field doesn't start at 1.

As you learned in Chapter 2, Access always generates AutoNumber values beginning with the number 1. (The only exceptions are if you're using random numbers or replications IDs, two rare choices that are described in Section 2.3.9.2.) However, there are plenty of reasons that you might not want Access to work this way. A company like Boutique Fudge might want its customer numbers to start at 1,000, its product numbers to start at 5,000, or its orders to start at 10,000. Numbering schemes like these often make for easier bookkeeping. They let you keep a consistent number of digits in your AutoNumber values, they help you distinguish between the IDs in different tables, and they help you avoid the embarrassment of telling a customer they just placed order number 1.

Thankfully, there is a (slightly awkward ) way to cheat the system and force Access to start at whatever number you want. Basically, you use an append query to do something you can't do on your own: Directly insert a record with a specific AutoNumber value. Once you create that record, Access keeps incrementing values starting from the new number you inserted. So if you append a record with the AutoNumber value 999, then Access gives the next record a value of 1000, and so on.

Here's how it all goes down:

Create a new table (Create Tables Table Design) .

You're going to keep this table around only for a few minutes.
Add one field. Give this field the same name as the AutoNumber field in the table you're trying to change .

Usually, this name's just ID.
Set the field to use the Number data type (not AutoNumber), and make sure its Field Size property's set to Long Integer (the standard choice) .
Right-click the table title, and then choose Datasheet View .

Save the table when Access prompts you, but don't worry about the nameTable1 is fine. When Access asks you if you want a primary key, just click No.
In Datasheet view, enter a value in the Number field of the temporary table that's 1 less than the starting value you want for the AutoNumber field .

If you want the AutoNumber field to start at 100, then enter 99 in the Number field. Close the table.
Create a new query (Create Other Query Design) .

In the Show Table dialog box that appears, pick the temporary table you created (like Table1), and then click Close.
Choose Query Tools Design Query Type Append to change this query into an append query .

When Access asks you what table to append to, choose the table that has the AutoNumber value you're trying to modify.
Double-click the field you added to your table (like ID) .

Access sets the Append To box to the same name, which is what you want.
Choose Query Tools Design Results Run to run the query .

Click Yes when Access warns you that it's about to add a record.
Open the table you just updated, and then delete the newly inserted record .

From this point onward, the AutoNumber values will keep incrementing from that number.
Delete the temporary table you created in step 1, because you don't need it anymore .

This technique has a few limitations. Namely, if your table has strict validation rulesfor example, one or more fields have the Required field property set to YesAccess doesn't let you insert the new record using the append query. In this situation, you need to either turn off your validation rules (by temporarily setting the Required field property to No for all fields), or add the required fields to your temporary table with the right values.