For many reports, building the source query is the most difficult step. Once you have the data you need and understand the grouping options and properties you can set, building the report is easy. We actually like to use the Report Wizard to get a jump-start on laying out our reports. The wizard works especially well when the record source contains 10 or fewer fields.
To start designing the Facility Occupancy By Date report, select the query in the Navigation Pane, and click the Report Wizard button in the Reports group on the Create tab. Build the basic report by taking the following steps:
On the first page of the wizard, choose the FacilityName, DateValue, RoomNumber, and EmpName fields, and click Next.
On the second page, the wizard suggests grouping the report by the RoomNumber field. However, you need to define custom grouping and sorting later, so click the left arrow to undo that selection, and then click Next.
On the next page, the wizard offers to sort the information for you. You can ask the wizard to establish some of the grouping and sorting settings you need by asking for an ascending sort on FacilityName, DateValue, and RoomNumber. Click Next.
Because you didn’t ask the wizard to create any groups, the wizard suggests a tabular layout, and this is just fine. Be sure that Orientation is set to Portrait and the Adjust The Field Width So All Fields Fit On A Page check box is selected. Click Next.
All the reports in this application use the Access 2007 style. Choose that style, and click Next.
On the final page of the wizard, enter Facility Occupancy By Date-2nd Quarter 2007 as the report title, select the Modify The Report’s Design option, and click Finish to create your report.
The report the wizard built should look like that shown in Figure 16–2.
Figure 16–2: This is the initial Facility Occupancy By Date report created by the Report Wizard.
Close the Field List window to give yourself more room in the object window. The wizard always places the report title label in the Report Header section, but that appears only once on the first page of the report. Especially when the report is likely to contain many pages, we like to see the report title repeated at the top of each page so that the subject of the report is clear throughout. You can move the report title from the Report Header section to the Page Header section. To do this, follow these steps:
Expand the bottom of the Page Header section about 0.5 inch.
Select any of the label controls in the page header. Move your mouse pointer in the middle of the label until it becomes double-sided crosshairs, and then drag the label down until it is close to the bottom edge of the Page Header section. You can also use the Down Arrow key to move the controls down to provide some space for the label from the Report Header section. Because the wizard has placed the labels and text boxes into a tabular control layout, you don’t have to worry about the labels becoming vertically misaligned from the text box controls in the Detail section. (Caution: If you hold down the Down Arrow key, Microsoft Office Access 2007 moves your selected controls down into the Detail section when they hit the bottom margin of the Page Header section.)
Click the label control in the Report Header section, and drag it down into the space you created in the Page Header section. You might want to select a column heading label again and use the Up Arrow key to close any gap below the bottom of the report title label control. Also, move the bottom of the Page Header section up so that there’s only a small space between the line and the bottom of the section.
Close up the bottom of the Report Header section so that it has zero height.
To make sure that all the characters in the report title label fit inside the control, click it and expand it to the right by grabbing the sizing handle in the center of the right edge. (Although the “size to fit” that the wizard performed on the label for you does a pretty good job, we find it safest to always slightly expand labels containing large fonts to make sure all characters show on the printed report.)
While you’re refining the look of the report, click the DateValue label control, and change its caption to Date. Also click the EmpName label control, and change its caption to Employee. (You can change captions directly in a label control by selecting the control, clicking inside it with your mouse, and then typing the new caption.) Select all the controls in the Page Footer section, and delete them. You’ll learn later how to create controls to display the current date and time and page numbers.
Your report should now look like Figure 16–3. Click the Save button on the Quick Access Toolbar to preserve your work to this point.
Figure 16–3: Your Facility Occupancy By Date report should look like this after you adjust what the wizard built.