Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
The information in this article clarifies how to produce and perform a make table query in Access. You employ a make table query once you must copy the data in a table, archive data, or perhaps save query results as a table.
If you’re required to alter or revise some of the data in an existing set of records, such as one or further fields, you can use an update query. For further information about update queries, see the article Create and run an update query.
If you need to add records (rows) to an existing table, you use an append query. For more information about append queries, see the article Add records to a table by using an append query.
What do you want to do?
Understand make table queries
A make table query gathers data from one or more tables, and then loads the result set into a new table. That new table can inhabit in the database that you have open, or you can design it in another database.
Usually, you form make table queries once 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 different table and deploying that table as a data source can shrink workload and offer a convenient data archive. During your progress, recall that the data in your new table is only a snapshot; it bears no relationship or connection to its source table or tables.
The process of creating a make table query follows these extensive steps:
- Enable the database, if it is not signed or if it does not reside in a trusted location. You are unable to 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 choose data from several tables and, in a real sense, you can de-normalise 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 normalised tables. You can even apply criteria in the query to further customise or whittle down your result set. For more information about normalizing your data, see the article Database design basics.
- Transform the select query to a make table query, select a location for the new table, and then run the query to make the table.
Do not confuse a make table query with an update or append query. You use an update query when you need to add or change data in individual fields. You use an append query when you need to add records (rows) to an existing set of records in an existing table.
Create a make table query
You create a make table query by first designing a select query, and then modifying it to a make table query. Your select query can utilise calculated fields and expressions to aid return the data that you need. The following steps detail how to form and convert the query. If you already have a select query that matches your goal(s), you can skip ahead to the steps for transforming the select query and performing 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.
- On the Create tab, in the Queries group, choose Query Design.
- In the Show Table dialogue box, double-click the tables from which you want to retrieve data. Each table displays as a window in the upper section of the query designer. Press Close once have finished adding the tables.
- 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.
- Optionally, append any expressions to the Field row.
- Optionally, create any criteria to the Criteria row of the design grid.
- Select Run to run the query and convey the results in a datasheet.
- Optionally, adapt your fields, expressions, or criteria and rerun the query until it yields the data that you want to place in your new table.
Convert the select query
- Launch your select query in Design View, or switch to Design View. Access offers multiple ways to do this:
- If you have the query open in a datasheet, right-click the document tab for your query and select Design View.
- If the query is closed, in the Navigation Pane, right-click the query and choose Design View on the shortcut menu.
- On the Design tab, in the Query Type group, pick Make Table.The Make Table dialogue box appears.
- In the Table Name box, state a name for the new table. -or- Choose the down-arrow and pick a current table name.
- Do one of the following:
- Place the new table in the current database
- i. If it’s still unselected, select Current Database, and then press OK.
- ii. Choose Run , and then press Yes to verify the operation.
- Place the new table in the current database
Note: If you are overwriting an existing table, Access first deletes that table and asks you to confirm the deletion. Choose Yes, and then select Yes again to create the new table.
- Place the new table in another database
i. Click Another Database.
ii. In the File Name box, enter the location and file name of the other database. -or- Pick Browse, use the new Make Table dialogue box to find the other database, and select OK.
iii. Choose OK to dismiss the first Make Table dialogue box.
iv. Press Run , and then select Yes to verify 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 mentioned query criteria and expressions. A query criterion is a rule that identifies the records that you want to include in a query, and you use criteria when you do not 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 Build an expression.
Stop Disabled mode from blocking a query
Automatically, if you open a database that is saved in another place other than a trusted location, or if you haven’t given your permission to trust the database, Access suspends all action queries — append, update, delete, or make-table queries — from processing.
If you endeavour to run an action query and it appears that nothing happens, review the Access status bar for the following message:
This action or event has been blocked by Disabled Mode.
After you see that message, do the following:
- On the Message Bar (just under the ribbon), press Enable Content.
- Start your query again.