Creating and Running a Make-Table Query

3 4

A make-table query is an action query that creates a new table based on existing data. You can even use criteria to restrict the results. There's one catch: The new table won't contain the original table's primary keys, indexes, or column and table properties (other than the defaults assigned to all tables).

Make-table queries are useful when you need to produce a table of filtered or summarized data. For example, you might want to run a make-table query to save data from a crosstab query based on several tables or queries. Such tables are useful when a complex query slows down performance-you can create a table and then base your forms and reports on that table instead of on the more complex query.

Troubleshooting - Data loses formatting when I export it

Say you export data, but some fields lose their formatting, and you must reformat the data before you can use it again. If this happens to you, chances are you're trying to export the results of a query, not a table, and Access tends to drop important formatting if you try to export the results of a query. The solution is to create a table and export the table instead of the query. This is especially true if you're working with a crosstab query-it's often more efficient to create a table from the crosstab query and work with that instead of working with the crosstab query itself, which can't be updated or exported.

To solve this loss of formatting problem, follow these steps:

• Create the appropriate query, including any formatting needs.
• Choose Query, Make Table Query, and enter an appropriate name for the new table.
• Run the query.
• Export the table, not the query.

  You can learn more about crosstab queries in Chapter 9, "Using Queries to Select Data."

Creating and running a make-table query is remarkably easy. To create a new table for orders shipped by Federal Shipping, follow these steps:

1. In the Database window, choose the Orders table.
2. From the New Object button's drop-down list, choose Query, and click OK in the New Query dialog box.
3. Drag all the fields to the design grid. (Don't use the asterisk [*] if you plan to use criteria expressions to limit records copied to the new table, as shown in the next step.)

   See the sidebar "Adding Multiple Fields to the Design Grid" in Chapter 9, "Using Queries to Select Data," for a comparison of dragging the asterisk vs. dragging all the fields to the design grid.

4. Specify appropriate criteria. As shown in Figure 10-16, I added the value 3 to the ShipVia field's Criteria cell. The ShipVia field is really a lookup field; it stores the shipping firm's unique value but displays the firm's name. Lookup fields are explained in Chapter 4, "Creating a Database."

   

   Figure 10-16. Use the make-table query to populate a new table with orders shipped by Federal Shipping.

5. Before running the query, choose Datasheet View from the View button's drop-down list to view the results of the query.

   note

   ---

   This step isn't as crucial when you're running a make-table query as it is with other queries because you can simply delete the new table if the query doesn't produce the results you want, but it's still a good idea.

6. Return to Design view, and choose Query, Make-Table Query.
7. Enter a name for the new table in the Make Table dialog box, shown in Figure 10-17, and click OK. I named the new table tblOrdersByFederalShipping.

   

   Figure 10-17. Enter a name for the new table.

   You can save the new table in another database by choosing the Another Database option. The Current Database option is the default.

8. On the Query Design toolbar, click Run to create the new table. Access will display the confirmation message shown in Figure 10-18. Click Yes to create the new table.

   

   Figure 10-18. Access asks you to confirm your intent to create a new table.

InsideOut

To make it easy to distinguish between tables created by make-table queries and regular tables in the Database window and drop-down lists, I like to use the tag tmak. For the make-table queries, I use the tag qmak, with the same base name for both the table and the query. Then when I see a table named tmakSpringOrders, I know that it was created by running the query qmakSpringOrders, so I won't waste time modifying table structure-instead, I'll know that I have to modify the make-table query and run it again to re-create the table.

The tags for the database objects are listed in Chapter 4, "Creating a Database."

You can run a make-table query even if the named table already exists, but Access will warn you first, as shown in Figure 10-19.

Figure 10-19. Access warns you when a make-table query attempts to create a table using the name of an existing table.

Clicking Yes will delete the existing table before the query creates the new table. (You'll get this second confirmation message only if there's a table of the same name.)

Here's the make-table query's SQL statement:

 SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID,  Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,  Orders.ShipVia, Orders.Freight, Orders.ShipName,  Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion,  Orders.ShipPostalCode, Orders.ShipCountry  INTO tblOrdersByFederalShipping FROM Orders WHERE (((Orders.ShipVia)=3)); 

This statement looks more complicated than it is because it has so many fields listed in the SELECT clause. The INTO clause is what makes this statement different from a SELECT clause. Because you specified a table name, SQL knows to create a new table instead of returning a recordset.

Troubleshooting - The table created by a make-table query doesn't have the right data type for some fields

You don't have control over the data type of fields in the table produced by a make-table query. If you need the output table to have fields of specific data types, prepare a table in advance, with all the fields set up with the appropriate data types, and then create an append query to append data to the target table.

If you need to create a table of data on a regular basis (say, for export of monthly sales results to an Excel worksheet), you can automate this process by making a backup copy of the target table so that you can make a fresh copy of the target table every time you need to append data to it.

caution

---

A make-table query won't use the underlying table's Caption property in the new table. Instead, the query defaults to field names.