• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 16. Advanced Report Design > Building a Query for a Complex Report

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 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 tbl-Facilities, tblReservations, and tblEmployees tables in the HousingDataCopy2.mdb database. Open a new Query window in Design view, and add these tables to the query.

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. To continue, add the fields listed in Table 16-1 to the design grid. (You can find this query saved as qryXmplRptReservationsByDay in the sample database.)


PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint