Create a New Table using a Make-Table Query
The Make-Table query
is classed as one of the Action
Queries, however this is different from the other types. Rather than
modifying the data contained in an existing database table, a Make-Table
query creates a new database table from the results of the query.
We can create the new table based upon a limiting criteria using
the make-table action query.
If we take a look at the following scenario, and example, we can
see why and how to use this object.
Make-Table Query Example
A large national company has approached your local business as they
are promoting a new product range in your area. The company would
like to produce mailing labels and send out form letters if you can
supply them with your recent Customer Information. The company have
stipulated that, as this is a trial mailing, they would like the details
of only those customers that have order products and that you have
dealt with over the past year.
Creating the Make-Table Query
Using our scenario, we will create a make-table query for all customers
from the specified area that have ordered products in the past 12
months. We will need to perform the following steps to create the
- Create a new query, use the Customers and Orders tables.
- From the Query Type button
on the toolbar, select Make Table
The Make Table dialog box appears, where you should enter the name
for the new table. Here we can also select whether we want to create
the new table in the current database or in another database. Ensure
that the current database is selected and click OK.
- Select the mailing information fields, in our case CustomerTitle,
CustomerName, Address, City, Postcode from the Customers table,
and OrderDate from the Orders table.
- Specify the chosen City criteria in the City field and add the
criteria required in the OrderDate field to only show records from
the last 12 months - using the DateAdd
The query design should appear like the example below:
- To check that the results are returned that we expect, click on
the datasheet button
on the toolbar. Once you have verified this, switch back to query
- In query design, deselect the Show: property for the OrderDate
field, as we do not need this to be visible in our new table.
- Click on the Run button
on the toolbar. Microsoft Access now displays a message to indicate
how many records will be copied to the new table.
- Click Yes to complete the query, and create the new table.
After completing the Make-Table query action, check your results
by opening the new table that you have created in the database window.
Note: When creating tables using the make-table query, the fields
in the new table will inherit the data types and field sizes from
the queries underlying tables. No other properties will be transferred.
To define a Primary Key or other properties in the newly created table
you will need to edit this in design view.
Make-Table queries can also be used to create back-up copies of your
database tables before you create action queries that may change the
contents of the tables (Update or Delete queries).