Ms Office Tune Up

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.)

1. Open the source database.

   In this example, that’s the Marketing.accdb database that has the contact information.

2. Create a new query by choosing Create Other Query Design.

   The Show Table dialog box appears.

3. 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.

4. 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.

5. 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.

6. 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 somewhere either 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.

7. Click OK to close the Append or Make Table dialog box.

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

9. 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.)

10. 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.

11. 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.

12. 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 places the 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.

13. 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.

Popularity: 6% [?]