Save query results as a table

A make-table query in an Access desktop database uses data you already have to make a new table. Find the query you want to use (or create a new one) and open it in Design view. On the ribbon, click Design, and then in the Query Type group click Make Table.

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.

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

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

In this article

Create a make table query

Stop Disabled mode from blocking a query

Create 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 query design grid. This figure shows the query design grid with several fields added:

   

4. Optionally, add any expressions to the Field row. You might do this if you need to combine two text fields from your source data into one text field in your new table; or, maybe you want to create a field and fill it with a specific value such as the current date. For help with expressions, see the article Build an expression.

5. Optionally, add any criteria to the Criteria row of the design grid. For help with criteria, see the article Examples of query criteria.

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

7. If the results aren't what you want, adjust your fields, expressions, or criteria and rerun the query. When it returns the data that you want to place in your new table, you're ready to convert the select query into a make-table query.

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.

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

Top of Page

Stop Disabled mode from blocking a query

When you open a database, depending on your Trust Center settings, Access may prevent all action queries— append, update, delete, and make-table queries— from running.

If you try to run an action query and it seems like nothing happens, check the Access status bar (on the bottom edge of the Access program window):

When you see that message, it means Access doesn't know whether you trust the database and has blocked things that might be unsafe – and since action queries change data, they are considered unsafe and are blocked.

To let your action query run, on the Message Bar (just under the ribbon), click Enable Content.

Top of Page