| A make-table query creates the new table structure from the structure of the records that underlie the query. Only the fields of the records that appear in the Datasheet view of the query are added to the new table's structure. If you design and save a tblShipAddresses table before extracting the duplicated data from the Orders table, you can use an append query to add the extracted data to the new table. To remove and then append records to the tblShipAddresses table, for example, follow these steps:  Open the tblShipAddresses table in Datasheet view, choose Edit, Select All Records, and then press the Delete key to delete all the records from the table. Click Yes when asked to confirm the deletion and then close the table.
 Open your make-table query, qryMTtblShipAddresses, from the Database window in Design view.
Tip Take extra care when designing action queries not to execute the query prematurely. If you double-click the query in the Database window or open the query in Datasheet view, you run the make-table query.  Choose Query, Append Query or use the Query Type toolbar icon and specify an append query. The Append dialog a renamed version of the Make Table dialog opens with tblShipAddresses as the default value in the Table Name drop-down list.
Click OK to close the Append dialog and add the Append To row to the Query Design grid (see Figure 13.8). Figure 13.8. Changing a select or make-table query adds an Append To row to the grid. You can specify appending values to a field by opening the Append To list for the query field and selecting the field name.  Tip To append data to a table, the field names of the query and of the table you are appending the records to must be identical, or you must specify the field of the table that the append query column applies to. Access doesn't append data to fields in which the field name differs by even a single space character. The Query Design grid for append queries has an additional row, Append To (shown in Figure 13.8), that Access attempts to match by comparing field names of the query and the table. Default values appear in the Append To row of columns for which a match occurs. If a match doesn't occur, open the Append To row's drop-down list and select the destination table's field.  Click Run to execute the append query. A message box displays the number of records that the query will append to the table. Click Yes to append the records, and then save the query.
Open the tblShipAddresses table to verify that you've added the 11 records.
 | If you can't add a primary key on a table you've appended new records to, see the "Appending Records Causes Primary Key Problems" topic of the "Troubleshooting" section near the end of the chapter. |
Jet SQL Append queries more commonly called INSERT queries add an INSERT INTO tablename(field list) clause to the SELECT statement. The field list argument is what lets you append data to a field with a different name. Following is the SQL statement for the INSERT version of the make-table query: [View full width] INSERT INTO tblShipAddresses ( CustomerID, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode,  ShipCountry ) SELECT DISTINCT Customers.CustomerID, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.ShipName<>Customers.CompanyName OR Orders.ShipAddress<>Customers.Address OR Orders.ShipCity<>Customers.City;
Like the select and make-table versions, the Jet SQL statement is SQL-92 compliant, so the preceding statement also executes as an SQL Server stored procedure. |
You can't append records containing values that duplicate those of the primary key fields or other fields with no-duplicates index in existing records. If you try to do so, a message box indicates the number of records that cause key-field violations. Unlike the paste append operation, however, Access doesn't create a Paste Errors table that contains the unappended records. |
