Microsoft Accesss 2010 : Enhancing the Queries That You Build - Creating and Running Action Queries (part 4) - Creating and Running Make Table Queries

Creating and Running Make Table Queries

Whereas an Append query adds records to an existing table, a Make Table query creates a new table, which is often a temporary table used for intermediary processing. You might want to create a temporary table, for example, to freeze data while you are running a report. By building temporary tables and running a report from those tables, you make sure users can’t modify the data underlying the report during the reporting process. Another common use of a Make Table query is to supply a subset of fields or records to another user.

Build a Make Table Query

To build a Make Table query, follow these steps:

1.	While in the query’s Design view, select Make Table from the Query Type group on the Design tab of the Ribbon. The dialog box shown in Figure 9 appears. Figure 9. The dialog box in which you enter a name for a new table and selecting which database to place it in.
2.	Enter the name of the new table and click OK.
3.	Move all the fields you want included in the new table to the query grid (see Figure 10). You will often include the result of an expression in the new table. Figure 10. Adding an expression to a Make Table query.
4.	Add to the query the criteria you want.
5.	Click Run on the toolbar to run the query. The message shown in Figure 11 appears. Figure 11. The Make Table query confirmation message box.
6.	Click Yes to finish the process.

If you try to run the same Make Table query more than one time, Access permanently deletes the table with the same name as the table you’re creating. (See the warning message in Figure 12.)

Figure 12. The Make Table query warning message that is displayed when an existing table already has the same name as the table to be created.

The SQL for a Make Table query looks like this:

SELECT tblTimeCards.TimeCardID, tblTimeCards.EmployeeID,
     tblTimeCards.DateEntered, [DateEntered]+365 AS ArchiveDate
     INTO tblOldTimeCards
     FROM tblTimeCards
     WHERE (((tblTimeCards.TimeCardID) Between 1 And 10));