Creating and running a select query is a way to extract information from a database without changing the data that is stored in the database's tables. A select query does not store data —it retrieves data that is stored in tables.
Sometimes you may want to create a new table by using the data that a particular select query retrieves. For example, suppose that you want to share some sales information with a business partner. You can create a select query that retrieves only the data that you want to share. But what if your business partner wants to reuse that data? To give your business partner a copy of the data, you may want to save the results of the select query as a new table, and then share that table with your business partner.
To save the data that is returned by a select query as a new table, you create and run a make-table query. When you run a make-table query, Microsoft Office Access 2007 creates a new table in the current database by using the data that is retrieved from one or more other tables in that database.
Note Although you can save a select query, the results of the select query are not saved. When you save a select query, for example, by pressing CTRL+S, you save the design of the select query —the question that the select query asks.
Save the results of a select query by using a make-table query
- Run the select query to verify that the results are what you expect.
- Make-table queries, like other action queries (action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Navigation Pane.), are not enabled in a database that is not in a trusted location and that does not have a security certificate. If the database that you are using does not enable action queries, the following message appears in the Message Bar beneath the Ribbon, part of the Microsoft Office Fluent user interface:
To enable action queries, on the Message Bar, click Options, and then click Enable this content.
If the Message Bar does not appear, action queries may already be enabled in the database that you are using, or you may need to display the Message Bar by doing the following:
- On the Database Tools tab, in the Show/Hide group, click Message Bar.
Note If Message Bar is unavailable (dimmed), there are no messages to display. In this case, action queries are already enabled.
- On the Home tab, in the Views group, click View, and then click Design View.
- On the Design tab, in the Query Type group, click Make Table.
The Make Table dialog box appears.
- By default, the new table is created in the current database. If you want to save the table in another database, click Another Database, and then type the path and file name of that database, or click Browse to browse for the database.
- In the Table Name box, type a name for the new table. If you want to replace an existing table, click the arrow in the Table Name box, and then, in the drop-down list, select the table that you want to replace.
- On the Design tab, in the Results group, click Run.
- If you are replacing an existing table, Office Access 2007 displays a warning that the existing table will be deleted. Click OK to confirm the table deletion.
A confirmation dialog box appears.
- In the confirmation dialog box, click Yes to paste the rows into a new table.
Top of Page