Creating a New Table with a Make-Table Query

Sometimes you might want to save as a new table the data that you extract with a select query. If you find that you keep executing the same query over and over against data that isn’t changing, it can be faster to access the data from a table rather than from the query, particularly if the query must join several tables.

In the last chapter, you created a very complex query using a Cartesian product to drive your PivotTable and PivotChart. At the end of each month or quarter, you might want to use a query like this to create a series of reports. When you have tens of thousands of reservations in your database, this complex query might take a long time to run for each report. You will save a lot of time if you first save the result of the complex query as a temporary table and then run your reports from that table. Also, after reservations are completed for a prior period of time, they’re not likely to change, so permanently saving the data selected by a query as a table could be useful for gathering summary information that you intend to keep long after you delete the detailed data on which the query is based.

Creating a Make-Table Query

In the Housing Reservations application, assume that at the end of each quarter you want to create and save a table that captures reservations detail for the quarter by facility, department, and employee. Open the HousingDataCopy.accdb sample database to follow along with the examples in this section. You might recall from the exercises in building the complex query to provide data for a PivotTable that you need to include tblDepartments, tblFacilities, tblEmployees, and tblReservations. You might also want to include a second copy of tblEmployees to capture the department manager and a copy of tblFacilityRooms to capture the room types. You’re essentially unnormalizing the data to create a single archive table that you can also use in reports.

As with most action queries, it’s a good idea to start with a select query to verify that you’re working with the correct data. Start a new query with tblFacilities, and add tblFacilityRooms, tblReservations, tblEmployees, and tblDepartments. Click the relationship line that the query draws between FacilityID in tblFacilities and FacilityID in tblReservations and press the Delete key to remove this extra join line. Be sure to also remove the extra relationship between the EmployeeNumber field in tblEmployees and the ManagerNumber field in tblDepartments. Add tblEmployees one more time-we plan to use the first instance of tblEmployees to get employees who have reservations, and the second instance to get the managers for the departments. Access names this second table tblEmployees_1 to avoid a duplicate name. Create a join line from EmployeeNumber in tblEmployees_1 to ManagerNumber in tblDepartments.

To avoid confusion with the two copies of tblEmployees, select tblEmployees_1 and click the Property Sheet button on the Design tab below Query Tools to open the property sheet shown in Figure 9–12. You can actually assign an alias name to any field list (table or query) in your query. In this case, change the name of the second copy of tblEmployees to Managers.

Figure 9–12: You can use the property sheet to assign an alias name to a field list in a query.

Now you’re ready to begin defining fields. Create an expression to display the employee name in the first field:

 EmpName:  tblEmployees.LastName & ", " & tblEmployees.FirstName & (" " + tblEmployees.MiddleName)

In the query grid, include the Department field from tblDepartments, and then add the manager name in the next column with an expression:

 MgrName: Managers.LastName & ", " & Managers.FirstName & (" " + Managers.MiddleName)

Notice that you’re using the new alias name of the second copy of tblEmployees. On the next Field line, add the ReservationID field from tblReservations, the FacilityName field from tblFacilities, the RoomNumber field from tblReservations, and the RoomType field from tblFacilityRooms. Add an expression in the next field to calculate the actual charge per day. Remember, you could use the DailyRate field from tblReservations, but that’s not an accurate reflection of how much the room costs per day when the employee stays a week or more. Your expression should look like this:

 DailyCharge: CCur(Round(tblReservations.TotalCharge / (tblReservations.CheckOutDate – tblReservations.CheckInDate), 2))

Your query design should now look something like Figure 9–13.

Figure 9–13: You can design a complex query to gather together many details about reservations.

Each row in tblReservations represents a stay of one or more days, but any report you create later might need to work with detail by individual day. To do that, you need to “explode” each single row in tblReservations into a row per day for the duration of the reservation. Recall from Chapter 8, that you’ll find a “driver” table-ztblDates-full of dates to accomplish this task. The table contains date values, one per day, for dates from January 1, 1992, to December 31, 2035.

Include this table in your query and notice that there’s no join line from it to any of the tables. When you add a table with no join defined to another table or set of records, the query returns the Cartesian product of the two sets of records-every row in the first table or set of records is matched with every row in the second table or set of records. In this case, each reservation will now be matched with each of the separate date values in ztblDates.

When you run this query later to create your working statistics table, you’re not going to want to have to open up the query each time in Design view to filter the dates. A couple of parameters would be a good idea here. Add the DateValue field from ztblDats and enter Between [Start Date] And [End Date] under this field on the Criteria line. Click the Parameters button in the Show/Hide group of the Design contextual tab, enter both parameters ([Start Date] and [End Date]), and set the data type to Date/Time. You have now provided a way to limit the “explosion” of rows to the dates of interest.

The final step is to further limit the rows created based on the CheckInDate and CheckOutDate fields in tblReservations. Any reservation that crosses the time span of interest is going to be for a few days or a few weeks. Add the CheckInDate and CheckOutDate fields from tblReservations, and clear the Show check box under both. In the Criteria row under CheckInDate, enter

 <=ztblDates.DateValue

Under CheckOutDate, enter

 >ztblDates.DateValue

This forces the query to keep any rows where DateValue from ztblDates is within the time span of each reservation row. You now have one row per date for each reservation. Your query should now look like Figure 9–14.

Figure 9–14: Build a complex parameter query to expand reservation details over a specified time span.

Switch to Datasheet view to verify that you’ll get the rows you want. The sample data contains reservations from February 28, 2007, through September 15, 2007. To get data for the second quarter of 2007, you can reply to the two parameter prompts with April 1, 2007, and June 30, 2007. Your result should look like Figure 9–15.

Figure 9–15: This is the recordset of the select query shown in Figure 9–14, for the second quarter of 2007.

To convert this select query to a make-table query, switch back to Design view and click the Make Table command in the Query Type group of the Design tab below Query Tools. Access displays the Make Table dialog box, shown in Figure 9–16. Type an appropriate name for the summary table you are creating, and click OK to close the dialog box.

Figure 9–16: In the Make Table dialog box, type a name for your summary table.

At any time, you can change the name of the table your query creates. Click the Property Sheet button in the Show/Hide group of the Design tab below Query Tools whenever the query is in Design view and change the Destination Table property. In this case, we entered a working table name. (We tend to prefix our working tables with the letter z to put them at the bottom of the table list.) After you run this query for a particular quarter, you’re probably going to rename the table to indicate the actual quarter’s worth of data that the table contains. You can find this make-table query saved in the sample database as qxmplReservationDetailsMakeTable.

Running a Make-Table Query

After you set up a make-table query, you can run it by clicking the Run command in the Results group on the Design tab below Query Tools. After you respond to the date parameter prompts, Access selects the records that it will place in the new table and displays a confirmation dialog box, as shown in Figure 9–17, that informs you how many rows you’ll be inserting into the new table.

Figure 9–17: This dialog box asks you to confirm the preliminary results of a make-table query.

Click Yes to create your new table and insert the rows. Click the menu at the top of the Navigation Pane, click Object Type under Navigate To Category, and click Tables under Filter By Group to bring up the table list, which should now include the name of your new table. Open the table in Datasheet view to verify the information, as shown in Figure 9–18.

Figure 9–18: The new table is the result of running the qxmplReservationDetailsMakeTable query.

Inside Out-Make-Table Query Limitations 

One of the shortcomings of a make-table query is it propagates only the field name and data type to the resulting table. Running the query does not set other property settings such as Caption or Decimal Places in the target table. This is why you see only field names instead of the original captions in Datasheet view. Notice also that the sequence of rows in the new table (Figure 9–18) does not match the sequence of rows you saw when you looked at the Datasheet view of your make-table query (Figure 9–15). Because the data in a table created with a make-table query has no primary key, Access returns the rows in the order that they’re stored physically in the database.

You might want to switch to Design view, as shown in Figure 9–19, to correct field names or to define formatting information. As you can see, Access copies only basic field attributes when creating a new table.

Figure 9–19: In Design view, you can modify the design of the table created by the qxmplReservationDetailsMakeTablequery.

At a minimum, you should define a primary key that contains the DateValue and ReservationID fields. You might also want to define default formats for the date/time fields. If you’re planning to create reports on this data that sort or group by department or facility, you should add indexes to those fields.