MS Access —

Append Queries

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.

The Append dialog box asks where you want to add the query's records.

An query in Design view.

There are several rules that you must follow when using an append query:

  • The appended data must meet the data validation and referential integrity rules of the table it is being added to.

  • The appended data must have its own unique primary-key values. If the primary-key field in the table to which the data is being added is an AutoNumber field, do not append that fieldAccess will generate new numbers for the new records.

  • The type of data in the records you're adding must match the type of data in the table to which you're adding them.

In this lesson you will create an append query to add a new group of tours to the tblCustomerTours table.

  1. If necessary, open the Lesson 6 database.

    Now let's create a new query.

  2. In the Database window, click the Queries icon in the Objects bar and click the New button.

    The New Query dialog box appears.

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

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

  5. Select Query » Append Query from the menu.

    The Append dialog box appears, as shown in figure. You have to tell the append query where you want the results of the query to be added.

  6. 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. Now you have to specify the fields you want to append.

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

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

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

  10. Double-click the Date, First Class, Smoker, Ship Via, and Cost fields in the tblCaribbeanTours field list.

    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.

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

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

  13. Click Yes.

    Access adds the records from the tblCaribbeanTours table to the tblCustomerTours table.

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

  15. Close the qryToursByName query.

How to create an append query:

  1. create a new query, select design view and click ok. click the tables and/or queries you want to use in the append query, click add, close.

  2. click the query type button list arrow on the toolbar and select append query.

    or...

    select query » append query from the menu.

  3. select the table to which you want to add the results of the query.

  4. if you select an existing table, click one of the following options:

    current database: if the table is in the currently open database.

    another database: type the name of the other database (including the path, if necessary).

  5. click ok.

  6. add the fields you want to append and identify a matching field if access doesn't supply one.

  7. click the view button on the toolbar to view the results of the query or the run button on the toolbar and click yes to confirm the addition.