Tables & Queries:
Make-Table Query

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

A Make-Table query Button: Make-Table Query turns the datasheet for the query into a new table. Such a query might select whole records from a single table or it might select various fields from several tables and queries. This kind of query makes copies of data and does not change the original records at all.

Why make a table instead of just running a query or report?

  • Static records: The records are frozen in time. A select query or a report is based on the current values. Is that what you want? The values in the new table will not change when the original data changes later, unless you run the make-table query again.

  • Speed: Viewing an existing table is faster than viewing a query datasheet or a report. For large numbers of records that you view frequently, this can be a big help.

  • Temp table: You may need a temporary table in the background as part of a complex procedure.

  • Export: You can create the table in a different database as a way of exporting the data.

WarningDanger with a make-table query: Running a Make-Table query again will replace the table that it created the last time. You will get a warning message if you attempt to do this.

WarningNo Undo: You cannot "undo" an action query's action.

TipVerify selection first: Be sure you have selected the records that you thought you did! Use the View button Button: Views - datasheet (2003) to see the query's datasheet. Check the records carefully.  Then run the action query with the Run button Button: Run (2003).


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


Example: Make-Table Query

The query Make Table-Zeroes-6thTerm from LanguageArtsClasses database brings fields from several tables and queries together. The purpose of the query is to make a table showing the assignments and tests in the 6th grading period that have a final score of 0.

Problem: I need to know who is missing which scores for the whole grading period. These are uncompleted assignments and tests.

One solution: Create a table that show this information along with the student's name.

Steps in the process:

  • Select: Choose the fields to include from various tables/queries for the new table.

  • Add Criteria: 6 in the Grading Period column and 0 in the Score column.
    I could have made this a parameter query by putting [Which grading period?] in the Criteria row for the Grading Period column. (This would actually have made more sense!)

  • Sort:  Classes/Subjects and NameFull sorted Ascending (out of view in the illustration below)

  • Which to Show: All fields except the Score field should have the Show box checked. All scores will all be 0! The Grading Period should show since I adjusted the criteria for each term and renamed the query.

  • Change the Select query to a Make Table query.

Query Design View: Make Table-Zeroes-6thTerm

Query Design View: Zeroes-6thTerm
(Some fields are out of view to the left)

  • View datasheet: Clicking the View button Button: Views - datasheet (2003) shows the selected fields and records but does NOT create the table. Always verify that your query is selecting the correct records!

    Query Datasheet View

     Query Datasheet View:  View query results BEFORE running the query

  • Run the query: Clicking the Run button Button: Run (2003)  actually starts the process to create the new table.
  • Message: You are about to paste 513 rows into a new table.Message: How many rows will be in the new table and warns that you cannot Undo this change.
  • Dialog: Make TableDialog: Name the new table. Notice that you can choose to create the table in a different database.
  • View the new table: Once you click on OK to name the table, the table is created but it does not open for viewing. You have to go to the Tables list in the Database Window to open the new table and see your results.

Why did I want such a table? I don't really remember! It is one of several ways to see who needed to do make-up work and for what assignment or test.

Icon: Step-by-Step 

Step-by-Step: Make-Table Query

 Icon: Step-by-Step

What you will learn:

to create a make-table query

Start with:  Class disk, Projects database open.

Making a new table out of existing data is often done for a temporary purpose. The table may help you with a complex analysis. It may be a way to export data to another database. Or, it may just be a convenient way to keep a copy of the data as it existed at a particular time.

For this project, you will create a table of "agents" of the World Travel Inc. travel agency.

Create a Select Query

  1. Create a new query in Design View, based on the table Staff.
     Include in your query the EmployeeID, the 3 name fields, City, Country/Region, Title, and DateHired.

  2. Sort the query on LastName, FirstName, MiddleName. (The fields must be in this order in the grid.)

  3. In the Criteria row for the column Title, add "agent".

     Query Design View: Make Table - Agents
    (Columns in the image were resized to reduce the width of the image)

  4. View the datasheet.
    Query Datasheet View: Make-Table Agents
    The query has selected records that have "Agent" in the Title column.

  5. Icon: Save Save the query as  Make-Table Agents.
    It is still just a Select query at this point.

Change Query to Make-Table Query

  1. Icon: Design Switch back to Design View.

  2. Button: Query Types - Make TableClick the button Query Types and select Make-Table Query....
    The Make-Table dialog appears for you to enter a name.

  3. Name the table Agents. Leave the choice Current Database selected.
     Dialog: Make Table - Make-Table Agents

  4. Title Bar: Make-Table Agents: Make Table QueryClick on OK. The query's name and type change in the Title bar but the query is still in Design View and has not run.

  5. Message: You are about to paste 5 row(s) into a new table.Icon: Run Run the query by clicking the Run button Button: Run (2003).
    A message box appears, telling you that how many rows you are about to add to a new table.

  6. Database Window: Tables - AgentsClick on Yes. The table is created but does not open.

View Results

  1. Icon: Design Switch to the Database Window

  2. If necessary, click on Tables to display the list of tables.

  3. Open the new table Agents. You should see the same records that you saw in the query's datasheet. The only difference is the window's Title bar.
    Table Datasheet View: Agents

  4. Table Design View: AgentsIcon: Design Switch to Table Design View for the Agents table. The field EmployeeID was the primary key in the original table. The values in EmployeeID are unique, since that field is the primary key for the Staff table.
    But Access did not automatically create a primary key for the new table. Unexpected.

  5. Select the field EmployeeID and click the Primary Key button Button: Primary Key (2003).

  6. Icon: Save Save the table and close it. (Agents)

TipWhether or not to have a primary key: There are situations when you do NOT want to have a primary key. Usually having a primary key is important to keeping the records in good order. Remember that a primary key must have a unique value for each record.