databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Make-Table Query

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 query:

  1. Create a new query, use the Customers and Orders tables.

    Creating the new query that we will use for the Make-Table query.
  2. From the Query Type button Query Type Button on the toolbar, select Make Table Make-Table query button

    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.

    The Make Table dialog box.
  3. Select the mailing information fields, in our case CustomerTitle, CustomerName, Address, City, Postcode from the Customers table, and OrderDate from the Orders table.
  4. 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 function: >=DateAdd("yyyy",-1,Now())

    The query design should appear like the example below:

    The Make-Table query design and criteria
  5. To check that the results are returned that we expect, click on the datasheet button Datasheet view button on the toolbar. Once you have verified this, switch back to query design view.
  6. In query design, deselect the Show: property for the OrderDate field, as we do not need this to be visible in our new table.
  7. Click on the Run button Run Query button on the toolbar. Microsoft Access now displays a message to indicate how many records will be copied to the new table.

    Microsoft Access message
  8. 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).