A 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
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
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.
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.
Undo: You cannot "undo" an action query's action.
selection first: Be sure you have selected the records that you
thought you did! Use the View button
the query's datasheet. Check the records carefully. Then run the
action query with the Run button
Where you are:
Project 1: Intro
Project 2: Access Basics
Project 3: Tables & Queries
Project 4: Forms & Reports
Example: Make-Table Query
The query Make
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
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
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
Change the Select query to a Make Table query.
Query Design View: Zeroes-6thTerm
(Some fields are out of view to the left)
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.
Step-by-Step: Make-Table Query
What you will learn:
|to create a make-table query|
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
For this project, you will create a table of
"agents" of the World Travel Inc. travel agency.
Create a Select Query
Create a new query in Design View, based on the
Include in your query the EmployeeID, the 3 name
Country/Region, Title, and
Sort the query on LastName,
FirstName, MiddleName. (The fields must be in this order in the
In the Criteria row for the column Title, add
(Columns in the image were resized to reduce the width of the image)
View the datasheet.
The query has selected records that have "Agent" in the Title column.
Save the query as Make-Table
It is still just a Select query at this point.
Change Query to Make-Table Query
Switch back to Design View.
the button Query Types and select Make-Table Query....
The Make-Table dialog appears for you to enter a name.
Name the table Agents.
Leave the choice Current Database selected.
Click 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.
the query by clicking the Run button
A message box appears, telling you that how many rows
you are about to add to a new table.
Click on Yes. The table is created but does
Switch to the Database Window
necessary, click on Tables to display
the list of tables.
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.
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.
Select the field EmployeeID
and click the Primary Key button
table and close it. (Agents)
Whether 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.