Create a make table query

You use a make table query when you need to copy the data in a table or archive data. A make table query is a select query that runs and then creates a new table based on the results. If you already have a select query you want to use as the basis for a make table query, use the procedure shown here. If you need some more information before you get started, go to the section Learn about make table queries, below.

Note   This article doesn’t apply to Access web apps – the kind of database you design with Access and publish online. See Create an Access app for more information.

  1. Open your select query in Design view, or switch to Design view.

  2. On the Design tab, in the Query Type group, click Make Table.

    The Make Table dialog box appears.

  3. In the Table Name box, enter a name for the new table.

    -or-

    Click the down-arrow and select an existing table name to replace that table.

  4. Choose whether to place the new table in the current database or in another database.

  5. Click OK.

  6. Click Run Button image, and then click Yes to confirm the operation.

To change or update part of the data in an existing set of records, such as one or more fields, you can use an update query. For more information about update queries, see the article Update data by using a query.

To add records (rows) to an existing table, you can use an append query. For more information about append queries, see the article Add records to a table by using an append query.

Learn about make table queries

Notes   

  • You can't create a make table query in an Access web app. However, you could export the source tables to an .accdb database in the Access desktop program, create and run a make table query there, and then import the new table into your Access web app.

  • You can't run a make table query in an Access Web database by using a Web browser. But you can run a make table query in an Access Web database that you open in the Access 2010 desktop program.

A make table query retrieves data from one or more tables, and then loads the result set into a new table. That new table can reside in the database that you have open, or you can create it in another database.

Typically, you create make table queries when you need to copy or archive data. For example, suppose you have a table (or tables) of past sales data, and you use that data in reports. The sales figures cannot change because the transactions are at least one day old, and constantly running a query to retrieve the data can take time — especially if you run a complex query against a large data store. Loading the data into a separate table and using that table as a data source can reduce workload and provide a convenient data archive. As you proceed, remember that the data in your new table is strictly a snapshot; it has no relationship or connection to its source table or tables.

The process of creating a make table query follows these broad steps:

  • Enable the database, if it is not signed or if it does not reside in a trusted location. You cannot run action queries (append, update, and make table queries) otherwise.

  • In query Design view, create a select query and then modify that query until it returns the records you want. You can select data from more than one table and, in a real sense, you can de-normalize your data. For example, you can place customer, shipper, and supplier data in a single table, something you would not do in a production database with properly normalized tables. You can also use criteria in the query to further customize or narrow your result set.

    For more information about normalizing your data, see the article Database design basics.

  • Convert the select query to a make table query, choose a location for the new table, and then run the query to create the table.

Build a select query and convert it to a make table query

You create a make table query by first creating a select query, and then converting it to a make table query. Your select query can use calculated fields and expressions to help return the data that you need. The following steps explain how to create and convert the query. If you already have a select query that fits your needs, you can skip ahead to the steps for converting the select query and running the make table query.

Create the select query

Note   If you already have a select query that produces the data that you need, go to the next steps.

  1. On the Create tab, in the Query group, click Query Design.

  2. In the Show Table dialog box, double-click the tables from which you want to retrieve data. Each table appears as a window in the upper section of the query designer. Click Close when have finished adding the tables.

  3. In each table, double-click the field or fields that you want to use in your query. Each field appears in a blank cell in the Field row of the design grid. This figure shows the design grid with several table fields added.

    A query with three fields in the design grid

  4. Optionally, add any expressions to the Field row.

  5. Optionally, add any criteria to the Criteria row of the design grid.

  6. Click Run Button image to run the query and display the results in a datasheet.

  7. Optionally, change your fields, expressions, or criteria and rerun the query until it returns the data that you want to place in your new table.

Convert the select query

  1. Open your select query in Design view, or switch to Design view. Access provides several ways to do this:

    • If you have the query open in a datasheet, right-click the document tab for your query and click Design View.

    • If the query is closed, in the Navigation Pane, right-click the query and click Design View on the shortcut menu.

  2. On the Design tab, in the Query Type group, click Make Table.

    The Make Table dialog box appears.

  3. In the Table Name box, enter a name for the new table.

    -or-

    Click the down-arrow and select an existing table name.

  4. Do one of the following:

    • Place the new table in the current database    

      1. If it isn't already selected, click Current Database, and then click OK.

      2. Click Run Button image, and then click Yes to confirm the operation.

        Note   If you are replacing an existing table, Access first deletes that table and asks you to confirm the deletion. Click Yes, and then click Yes again to create the new table.

    • Place the new table in another database    

      1. Click Another Database.

      2. In the File Name box, enter the location and file name of the other database.

        -or-

        Click Browse, use the new Make Table dialog box to locate the other database, and click OK.

      3. Click OK to close the first Make Table dialog box.

      4. Click Run Button image, and then click Yes to confirm the operation.

        Note   If you replace an existing table, Access first deletes that table and asks you to confirm the deletion. Click Yes, and then click Yes again to create the new table.

Learn more about query criteria and expressions

The steps in this article mention query criteria and expressions. A query criterion is a rule that identifies the records you want returned from a query. You use criteria when you don't want to see all the records in a given set of data. For example, the criterion >25 AND <50 returns values greater than 25 and less than 50. A criterion such as "Chicago" OR "Paris" OR "Moscow" returns only the records for those cities.

For more information about using criteria, see the article Examples of query criteria.

An expression is a combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. You use an expression when you need data that does not reside directly in a table. For example, the expression [UnitPrice]*[Quantity] multiplies the value in the UnitPrice field by the value in the Quantity field. You can use expressions in a wide variety of ways, and the process of creating and using them can become quite complex.

For more information about creating and using expressions, see the article Create an expression.

Stop Disabled mode from blocking a query

By default, if you open a database that isn't saved in a trusted location, or if you haven't chosen to trust the database, Access prevents all action queries — append, update, delete, or make-table queries — from running.

If you try to run an action query and it seems like nothing happens, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

When you see that message, do the following:

  • On the Message Bar (just under the ribbon), click Enable Content.The Message Bar

Top of Page

Applies To: Access 2013, Access 2010



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language