MS Access —

Make-Table Queries

Like all queries, a make-table query asks a question of the information in one or more tables and then retrieves results. Instead of displaying the results, however, a make-table query creates a new table with the results of the query.

Type the name of the table you're creating in the Make Table dialog box

The make-table query

The query uses its results to create a new table.

Make-table queries are useful for:

  • Exporting a table to another database or application.

  • Creating a backup copy of a table.

  • Creating an archive table that stores old records.

  • Creating a table that includes information or fields from more than one table.

In this lesson you will create a make-table query to create a table with information about all China tour records.

  1. Click the qryTourSales query and then click the Design button.

    The query opens in Design view. First you need to add the fields that you want to include in your new table.

  2. Double-click the TourName field in the tblTours field list, the Number of Tickets, Date, and Cost fields in the tblCustomerTours field list, and the LastName and FirstName fields in the tblEmployees field list.

    The make-table query will create a table with these fields, as shown in Figure. Next you need to specify any limiting criteria.

  3. Click the TourName column's Criteria row and type China.

    Access will add the "quotation marks" around the text string "China" for you. The make-table query will only include China tours when it creates the new table.

    Here's how to change the query type to a make-table query:

  4. Click the Query Type button list arrow and select Make-Table Query from the list.

    Access displays the Make Table dialog box, as shown in Figure. Here you need to tell Access the name of the new table.

  5. Type tblChinaTours in the Table Name box and click OK.

    You're ready to have the make-table query create the new table. Preview the results of the query first.

    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 make-table query in Datasheet view.

  6. Click the View button on the toolbar to preview the results of the make-table query in Datasheet view.

    The make-table query displays the records it will use to create the new tblChinaTours table.

  7. 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 creation of the tblChinaTours table.

  8. Click Yes.

    Access creates the new tblChinaTours table based on the results of the make-table query, as shown in Figure. Now let's open the new table.

  9. Close the query without saving your changes, click the Tables icon in the Objects bar, and double-click the tblChinaTours table.

    Verify the results of the make-table query, then...

  10. Close the tblChinaTours table.

To create a make-table query:

  1. in design view, create a select query; including any tables, fields, calculated fields, and criteria.

  2. click the query type button list arrow on the toolbar and select make-table query.

    or...

    select query » make-table query from the menu.

  3. type the name of the table you want to create, or click the drop-down list and select a table from the list if you want to replace the existing one.

  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: and type the name of the other database (including the path, if necessary).

  5. click ok.

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