Make Table Queries in Microsoft Access 2013

Make Table Queries in Microsoft Access 2013: Video

This video shows you how to create Make Table queries in Access 2013. For the complete tutorial click here.

Make Table Queries in Microsoft Access 2013: Overview

Have you ever run a query and wished that you could save the result set of the query as a permanent table? In Access, that is exactly what the “Make Table” queries do. A “Make Table” query creates a new table as the output of a query, instead of simply displaying a query result set. This new permanent table can be useful in a variety of ways: you could use it as a basis for other queries; it could serve to backup important data; or it could show selected information from multiple underlying tables in a single table which you could then export to Microsoft Excel for spreadsheet-style analysis. Since the basis of the make table query is a standard ‘select’ query-type, you can restrict the columns and rows that are displayed in the new table by picking just the fields you wish to view and applying query criteria.

The fields that comprise the new table created from the make table query’s result set retain the data type and field size properties that were assigned to the original fields. However, other properties that you may have set for the base table’s fields will not be inherited by the new fields. So, you may need to re-enter validation rules, and other properties in the new table, if needed.

To create a make table query, first create a standard query in design view. Select only the fields that you want to include in your new table, and use criteria to restrict the rows selected, if needed. Then click the “Make Table” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab in the Ribbon to change the query type.

In the “Make Table” dialog box that appears, you can type a name for the new table that will be created when you run the query. You can also select whether to place the new table into the current database, or into another database by selecting the desired option button. If you select “Another Database:,” then you will have to click the “Browse…” button and use the dialog box that appears to select the database file into which you want to place the new table. When you have selected where to place the new table, click “OK” in the “Make Table” dialog box.

Once you are ready, you can run the query to create a new table. When you run a “Make Table” query that was based on a “Select” query, Access will delete the “Select” query. So be sure to save a copy before running the “Make Table” query. A dialog box will remind you of this if you forget.

Next, you will see a dialog box appear that confirms how many rows you are about to paste into a new table. Click the “Yes” button in the dialog box to paste the selected rows and fields into a new table.