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.

image from book
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.




Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

Similar book on Amazon
Access 2007: The Missing Manual
Access 2007: The Missing Manual
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoft Office Access 2007 Forms, Reports, and Queries
Microsoft Office Access 2007 Forms, Reports, and Queries
Alison Balter's Mastering Microsoft Office Access 2007 Development
Alison Balter's Mastering Microsoft Office Access 2007 Development

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net