Building a Query for a Complex Report

To explore some of the advanced features you can include in a report, let’s build a report in the Housing Reservations database that displays room occupancy information by facility, date, room, and employee. As noted in the previous chapter, reports tend to bring together information from many tables, so you are likely to begin constructing a report by designing a query to retrieve the data you need for the report. For this example, you need information from the tblFacilities, tblReservations, and tblEmployees tables in the HousingDataCopy2.accdb database. Open a new Query window in Design view by clicking the Query Design button in the Other group on the Create tab, and add these tables to the Query window.

The tblReservations table contains one row per reservation, and the reservation could span many days. If you want to report on occupancy by day, you must use the special trick you learned in Chapter 8, “Building Complex Queries”: Include a table containing all the dates you want, and add special criteria to expand each row in tblReservations into one row per day. The sample database contains a handy table, ztblDates, that has rows containing all the dates from January 1, 1992, to December 31, 2035, so add that table to your query. Close the Show Table dialog box. Next, add the fields listed in Table 16–1 to the design grid. (You can find this query saved as qryXmplRptReservationsByDay in the sample database.)

Table 16–1: Fields in the qryXmplRptReservationsByDay Query
Open table as spreadsheet

Field/Expression	Source Table	Criterion
EmpName: tblEmployees.LastName & “, ” & tblEmployees.FirstName & (“”+tblEmployees.MiddleName)
ReservationID	tblReservations
FacilityName	tblFacilities
RoomNumber	tblReservations
DateValue	ztblDates	Between #4/1/2007# And #6/30/2007#
CheckInDate	tblReservations	< =[ztblDates].[DateValue]
CheckOutDate	tblReservations	>[ztblDates].[DateValue]
TotalCharge	tblReservations

Your Query window should look similar to the one shown in Figure 16–1.

Figure 16–1: The qryXmplRptReservationsByDay query for the Facility Occupancy By Date report returns one row per day in each reservation.

You might be wondering why the query has a criterion to limit the range of dates returned from the ztblDates table. The sample database contains reservations from February 18, 2007, through October 9, 2007 (340 records). Although you could certainly create a report that includes all reservations, a user is typically going to want to look at records only for a specific date span. (For example, the housekeeping department might be interested in seeing this report only for the next few days or week.) Also, because the ztblDates table contains more than 16,000 rows, this query could take up to a minute to run-or more on a slow computer-unless you filter the rows. In Chapter 20, “Automating Your Application with Visual Basic,” you’ll learn how to provide the user with a custom date range dialog box to limit the records. For this example, the query includes a filter to limit the rows to the second quarter of 2007.

You can either save your query as qryMyRptReservationsByDay and select it in the Navigation Pane, or select the qryXmplRptReservationsByDay query in the Navigation Pane to follow along in the next section.