Creating and Embedding a Subreport
In many of our reports, you will probably design the Detail section to display a single line of information from the underlying record source. As you learned earlier in this chapter, it's fairly easy to link several tables to get lots of detail across several one-to-many relationships in our database. You also saw how to use the Hide Duplicates property to display a hierarchy across several rows of detail.
However, as with forms and subforms, which you learned about in Chapter 15, "Advanced Form Design" you can embed subreports in the detail section of our report to display multiple detail lines from a table or query that has a "many" relationship to the one current line printed in the detail section. This technique is particularly useful when you want to display information from more than one "many" relationship on a single page. In the Microsoft Press Books database, for example, the rptAuthors report combines the basic author information in the main report with the list of all books the author has written in a subreport. The rptBooks report prints the main book information as the detail, all of the classification categories in one subreport, and all of the authors in another subreport. You could create a very complex query that joins all the information, but you'd get one row for each unique combination of book, book category, and author per book—far more rows than you actually need to solve the problem.
Ray McCann, the owner of RM Productions, presented me with a particularly interesting and challenging problem that I solved with subreports, as you'll see in the following sections. Although an entertainment contract might span several weeks, all of the clubs and groups that Ray deals with like to schedule on a week-to-week basis. He needed a report that would show him, for each of several upcoming weeks, which clubs and groups for which he acts as an agent were not yet booked. Armed with this report, he could easily create a working list of unbooked groups that could be of interest to the open clubs. It would then be a simple matter of calling each club manager to suggest an available group and perhaps book it on the spot. Also, if a club or group for which he is not the usual agent were to call him to request a booking during a certain week, he could quickly generate this report to let the caller know what is available.
As you'll see in the following sections, queries can extract the open club or group information that Ray needs. A creative use of subreports puts all this information on one page per week to make it extremely easy to use.
Building the Subreport Queries
If you read to the end of Chapter 11, "Advanced Query Design—SQL" you already learned about some advanced techniques for wringing information out of the tables in the Entertainment Scheduling database. The queries to solve the "open club or group" problem use some of the same techniques.
What makes this problem difficult is that the tblContracts table contains only one row per contract for a time period that might span several weeks. It doesn't make sense to store one row per week—that would mean a lot of redundant data. But as mentioned earlier, clubs and groups like to book (and take care of payments) on a week-to-week basis. (In Chapter 23, "Automating our Application with Visual Basic" you'll learn about some special procedures in the frmContracts form that set up the necessary weekly payment records in tblCommissions.) The queries to solve this problem use a "driver" query to separate each contract row into multiple weekly booking rows to perform the necessary analysis.
Since we're trying to find open weeks, we first need a query that generates one row per club or group for each available week on the calendar. You'll use a query that lists all clubs for all weeks in an outer join with a query that lists the booked weeks for each club to determine which weeks are open for the club. There's a companion set of queries that deal with groups.
In the sample database, you can find a table called tblDates that lists all dates from January 1, 1992, through the end of the year 2035. The query named qryMondays creates a row for Monday of each week (the start of the week for most group and club bookings). If you include this query in another query along with either the tblClubs table or the tblGroups table, you can generate one row per club or group for each week. You can see the query for clubs in Attached Figure 18-25. (The query has been optimized by adding a parameter to reference the main report that will include the subreport that uses this query.)
Attached Figure 18-25. A query to return one row for each week for all clubs.
To build the new query, you'll need the ClubID, ClubName, MgrFirstName, MgrLastName, and ClubPhoneNumber fields from the tblClubs table. Because there is no join line between the table and the query, you get the Cartesian product of the rows in the two record sources. That is, you get each row in tblClubs matched up with each row in qryMondays. That's a lot of rows. In most cases, Ray will want to look at only a few weeks at a time. Rather than extract all rows every time this query runs, you can restrict the rows returned by the query to only the weeks of interest.
When I built this query, I knew that I was going to use it in a subreport embedded in a main report called rptOpenClubsAndGroups. Furthermore, I knew that the main report would use the SchedDate field from qryMondays to print one week per page. You'll learn more about how to reference open forms and reports in Chapter 21, "Automating our Application with Macros" For now, it's sufficient to know that the expression [Reports]![rptOpenClubsAndGroups]![SchedDate] references the value in the SchedDate field on the current page of the report we're about to build. This limits the query to one week per page so that the report takes a few seconds per page to calculate instead of several minutes. You can find the qryAllClubsAllDates and qryAllGroupsAllDates queries in the sample database.
Now we need a query to produce one row per booked week for every contract. You can start with a query that joins tblClubs to tblContracts. You can output from this query one row per contract that includes the club name, manager name, club phone number, group name, group leader name, group phone number, contract status, and beginning and ending dates of the contract. To get one row per week, you can add our old friend qryMondays to the mix and use a special type of join to get the required result. Remember, if you don't create a join between qryMondays and the other tables, you'll get one row for all weeks for all contracts. What you want is one row from the join across clubs and contracts linked with each row from qryMondays that represents a week within the span of the contract. As long as the Monday from qryMondays is not more than seven days earlier than the start of the contract and that same date is also less than or equal to the date of the end of the contract, at least part of the contract will fall during that week.
The only way to specify this sort of join is in SQL. You can see the query (which is saved as qrySchedDates in the sample database) in Attached Figure 18-26. (This query is also optimized to reference the outer report.) If you studied Chapter 11, "Advanced Query Design—SQL" you know that the list following the SELECT includes all the fields to be output by the query. The FROM clause lists the source tables or queries and specifies how Access should link them. The "magic" part of the query is this phrase:
(qryMondays.SchedDate <= tblContracts.EndingDate) AND
(qryMondays.SchedDate > tblContracts.BeginningDate - 7)
This states in "SQL-ese" the link criteria necessary to get one row per week. In English, the FROM clause means, "Link the rows from qryMondays with the rows from tblContracts where the week defined by the row in qryMondays spans any part of the contract in tblContracts." As with the qryAllClubsAllDates query, the WHERE clause restricts the output of this query to the date on the current page of the report to get the best performance.
Attached Figure 18-26. A complex query that returns one row per week for each contract (which might span multiple weeks) in the database.
Attached Figure 18-27. A complex query that returns one row per unbooked week for each club.
You can find qryOpenRMPClubs and qryOpenRMPGroups in the sample database; they return the open weeks for clubs and groups, respectively. The next step is to use these queries as the row source for two subreports.
Designing the Subreport
Select either query in the Database window, and select Report from the New Object toolbar button's drop-down list. Select Design View in the New Report dialog box, and click OK to open the Report window in Design view.
Open the View menu, and check that the Page Header/Footer command is not selected. Access won't display page headers and footers in a subreport. In the final report, the list of open clubs should appear side-by-side with the list of open groups, so each subreport should be narrow enough to fit both of them across a standard-width page—about 3 inches will work. You don't need the SchedDate field from the underlying query on the report, but that field will form the link to the main report, as you'll see in a moment. For clubs, you need the ClubName, Manager, and ClubPhoneNumber fields, as shown in Attached Figure 18-28.
Attached Figure 18-28. The design of the subreport to display unbooked club data.
For Groups, you need the GroupName, Leader, and HomePhone fields, as shown in Attached Figure 18-29.
Attached Figure 18-29. The design of the subreport to display unbooked group data.
For both subreports, choose Report Header/Footer from the View menu, and shrink the header to a height of 0. Make the report footer about 0.125 inch tall, and place a thick black line control across the top of the section. If the list is long enough to span more than one page, this line will provide a visual indicator of the end of the list. You can find the two subreports saved as rsubOpenClubs and rsubOpenGroup in the sample database.
Embedding the Subreport
Now comes the payoff. Start a new report on the qryMondays query. Make the report 6.5 inches wide. (This works well with the default 1-inch margins.) Create a title in the Page Header section using a label control, and add a text box to display today's date if you want. Drag the SchedDate field from the field list onto the Page Header section. Change the text box's caption to Week Beginning. Add a label on the left side for clubs and another on the right side for groups. In the Detail section, add two subreport controls next to each other, and make them both about 3 inches wide and 0.5 inch high.
To embed the rsubOpenClubs subreport in the control at the left, enter Report.rsubOpenClubs in the Source Object property box of the subreport control, as shown in Attached Figure 18-30 below. Because you can also include a form in the report, the Report prefix tells Access to include a report, not a form. As you did with a subform, you need to define linking fields. In this case, the SchedDate field on the main report (which is set in the Link Master Fields property box) matches the SchedDate field on the subreport (which is set in the Link Child Fields property box). You need to set both the Can Shrink and Can Grow properties to Yes to allow the subreport to expand or shrink as necessary.
Attached Figure 18-30. The subreport is linked to the main report.
Follow the same procedure to embed the rsubOpenGroup report in the subreport control on the right. Finally, choose the Size command from the Format menu, and then select To Fit from the submenu so that both subreport controls are sized correctly to display all the data in the subreports.
Access 2000 introduces a new feature that allows us to directly edit our subreport once we have defined it as the source for our subreport control. As we can see in Attached Figure 18-30, the design of both of the subreports is visible in the respective subreport controls. we can click any control in the inner report and change its size or adjust its properties using the property sheet or the Formatting toolbar. we may need to temporarily expand the size of the subreport control in order to work with the inner report easily. Note, however, that we cannot open the inner report in Design view separately from the Database window as long as we have the outer report open. we also cannot use File / Save As to save our changes to a different report definition.
Remember that three of the queries contain references to the main report's SchedDate field to obtain optimum performance. If we want to save our own copy of this report, we must also go to each of the three queries and change the reference to point to our report. we can then open our own report to see how it works. Because our report isn't restricted in any way, we'll see open dates starting with January 1, 1992. If we want to see how this report works in the "live" application, go on to the next section.
Viewing the Embedded Result
To see how this report works in "real life," go to the Database window and open the frmMain form. Click the Reports button on that form to open the Reports switchboard form. On that form, click the Open Clubs And Groups button to open a dialog box in which we can specify a date range, as shown in Attached Figure 18-31.
Attached Figure 18-31. Specifying a date range for the Open Clubs And Groups report.
The sample database contains contract data from July 1999 to December 2000. If we specify a date range beginning in late October 1999 and click the Print button in the Print Open Dates dialog box, we should see the result shown in Attached Figure 18-32 below. Do you suppose the No One Wants to Work Here club would be interested in booking Apes of Wrath? Better ask Stephanie first!
Attached Figure 18-32. The Open Clubs And Groups report showing a result from two subreports.
 Attachments
Creating and Embedding a Subreport (34201-19639-Figures.zip)
|