Share this Page URL

Chapter 9. Extending Your Use of Queries > Running Action Queries - Pg. 317

Extending Your Use of Queries 317 When designing an append query, you identify the fields the query draws from the source table or query and the fields this data will be appended to in the target. The target table can be in the current database or in a separate database. Running an append query doesn't create the target table. For the query to run successfully, the target table must already be defined and its structure (its fields and their data types) must be compatible with the data you are appending. As with other queries, you can use criteria in an append query to select specific records. In this example, we'll create a table that we'll use to store older orders (the OldOrders table), and then we'll use an append query to add to that table the records that meet the criteria of an old order. Archive records with an append query 1. 2. In the Database window, click Tables in the Objects list, and then select the Orders table. Click Edit, Copy, and then click Edit, Paste. In the Paste Table As dialog box, shown here, enter OldOrders for the table's name. Select Structure Only (Local Table) in the Paste Options area, and then click OK. 3. 4. 5. 6. By selecting the Structure Only option, we create a compatible target table that holds no data. Click Queries in the Database window, and then double-click Create Query In Design View. In the Show Table dialog box, select Orders, click Add, and then click Close. Click OrderID at the top of the field list, hold down the Shift key, scroll down the field list to ShipCountry (the last field in the list), and then click that field. Drag all the fields to the query design grid. Each field will populate a column in the query design grid. Although we will include all the fields in the archived records, we can't use the asterisk in this case to add the table's fields to the query design grid because we'll add criteria to the Order- Date field to append only specific records. If you used the asterisk and added the OrderDate field as well, you'd see an error message when you ran the query because you'd be trying to append data to the OrderDate field twice. In the Criteria row for the OrderDate field, enter <#1/1/97#, as shown here: