An append query takes a group of records from one or more tables or queries in your database and adds them to another existing table. Append queries are especially useful for importing information into a table. For example, you could use an append query to import several dozen customer records from an Excel spreadsheet into an existing table. Of course, you would have to know how to import the Excel spreadsheet firstand that's another lesson in and of itself.
In this lesson you will create an append query to add a new group of tours to the tblCustomerTours table.
If necessary, open the Lesson 6 database.
Now let's create a new query.
In the Database window, click the Queries icon in the Objects bar and click the New button.
The New Query dialog box appears.
Select Design View and click OK.
The query design window and Show Table dialog box both appear. Let's select the tables and/or queries you want to use in the append query.
Click the tblCaribbeanTours table, click Add, and then click Close.
Now you need to convert the select query to an append query. Here's how:
Select Query The Append dialog box appears, as shown in Figure 6-31. You have to tell the append query where you want the results of the query to be added.
Select tblCustomerTours from the Table Name list and click OK.
The append query will add the results of its query to the tblCustomerTours table. Notice that an Append To row appears in the design grid, as shown in Figure 6-32. Now you have to specify the fields you want to append.
Double-click the CustomerID, Employee, and TourID fields in the tblCaribbeanTours field list.
Since the field names are the same in both tables, Access automatically fills in the Append To row with the names of the fields you're appending records to. If some of the fields you're appending have a different field name, you will have to specify to which field they should be added from the Append To row.
Double-click the No Tickets field in the tblCaribbeanTours field list.
Since there isn't a field named No Tickets in the tblCustomerTours table, Access doesn't automatically fill in the Append To row. You will have to select the name of the field you want to to.
Click the Append To row in the No Tickets field, click the list arrow, and select Number of Tickets.
This will append the fields from the No Tickets field in the tblCaribbeanTours table to the Number of Tickets field in the tblCustomerTours table. Move on to the next step and finish adding the remaining fields that you want to append.
Double-click the Date, First Class, Smoker, Ship Via, and Cost fields in the tblCaribbeanTours field list.
Note: As with any action query, you should always preview the results in Datasheet view first. Click the View button on the toolbar to display the results of the Append query in Datasheet view.
Click the View button on the toolbar to preview the results of the append query in Datasheet view.
The append query displays the records it will add or append to the tblCustomerTours table.
Click the View button on the toolbar to return to Design view and click the Run button on the toolbar.
Access asks you to confirm the addition of the records to the tblCustomerTours table.
Access adds the records from the tblCaribbeanTours table to the tblCustomerTours table.
Close the query without saving your changes and open the qryToursByName query.
Notice the new tblCaribbeanTours records that have been added by the append query.
Close the qryToursByName query.