Tables & Queries:
Make-Table Query

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 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.

Danger 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.

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

Verify selection first: Be sure you have selected the records that you thought you did! Use the View button to see the query's datasheet. Check the records carefully.  Then run the action query with the Run button .

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: Zeroes-6thTerm
(Some fields are out of view to the left)

• View datasheet: Clicking the View button 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:  View query results BEFORE running the query

• Run the query: Clicking the Run button   actually starts the process to create the new table.
• Message: How many rows will be in the new table and warns that you cannot Undo this change.
   
• Dialog: 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.

Step-by-Step: Make-Table Query

---

What you will learn:

to create a make-table query

---

Start with:  , 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".

    
   (Columns in the image were resized to reduce the width of the image)
    

4. View the datasheet.
   
   The query has selected records that have "Agent" in the Title column.
    

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

---

Change Query to Make-Table Query

1. Switch back to Design View.
    

2. Click 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.
    
    

4. 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.
    

5. 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.
   

6. Click on Yes. The table is created but does not open.
    

---

View Results

1. 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.
   
    

4. 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 .
    

6. Save the 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.
 

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012