Chapter 9: Presenting Data with Access Reports
Reports provide the most flexible way of viewing and printing summarized information. Reports display information with the desired level of detail, while enabling you to view or print your information in almost any format. You can add multilevel totals, statistical comparisons, and pictures and graphics to a report. In this chapter, you learn to use Report Wizards as a starting point. You also learn how to create reports and what types of reports you can create with Access.
In this chapter, you create new reports using the report wizards and by creating a blank report without using a wizard. You use tables created in previous chapters. The Chapter09.accdb database file on the book’s CD-ROM contains the completed reports described in this chapter.
Understanding Reports
Reports present a customized view of your data. Report output is viewed on-screen or printed to provide a hard copy of the data. Reports provide summaries of the information contained in the database. Data can be grouped and sorted in any order and can create totals that add numbers, calculate averages or other statistics, and graphically display data. Reports can include pictures and other graphics as well as memo fields in a report. If you can think of a report you want, Access probably supports it.
Understanding report types
Four basic types of reports are used by businesses:
• Tabular reports: These print data in rows and columns with groupings and totals. Variations include summary and group/total reports.
• Columnar reports: These print data as a form and can include totals and graphs.
• Mail-merge reports: These create form letters.
• Mailing labels: These create multicolumn labels or snaked-column reports.
• Graphs: Visual representation of your data in a form such as a bar or a pie chart.
Tabular reports
Figure 9-1 is a typical tabular-type report (rptProductsSummary) displayed in print preview. Tabular reports (also known as groups/totals reports) are similar to a table that displays data in neat rows and columns. Tabular reports, unlike forms or datasheets, usually group data by one or more fields. Often, tabular reports calculate and display subtotals or statistical information for numeric fields in each group. Some reports include page totals and grand totals. You can even have multiple snaked columns so that you can create directories (such as telephone books). These types of reports often use page numbers, report dates, or lines and boxes to separate information. Reports may have color and shading and display pictures, business graphs, and memo fields. A special type of summary tabular report can have all the features of a detail tabular report but omit record details.
Figure 9-1
A tabular report (rptProductsSummary) displayed in Print Preview
Columnar reports
Columnar reports generally display one or more records per page, but do so vertically. Columnar reports display data very much as a data-entry form does but are used strictly for viewing data and not for entering data. Figure 9-2 shows part of a columnar report (rptProducts) in Print Preview.
Figure 9-2
A columnar report showing report controls distributed throughout the entire page
Another type of columnar report displays one main record per page (like a business form) but can show many records within embedded subforms. An invoice is a typical example. This type of report can have sections that display only one record and at the same time have sections that display multiple records from the many side of a one-to-many relationship—and even include totals.
Figure 9-3 shows an invoice report (rptInvoice) from the Access Auto Auctions database system in Report view.
In Figure 9-3, the information in the top portion of the report is on the “main” part of the report, whereas the product details near the bottom of the figure are contained in a subreport embedded within the main report.
Mailing labels
Mailing labels are also a type of report. You can easily create mailing labels, shown in Figure 9-4, using the Label Wizard to create a report in Access. The Label Wizard enables you to select from a long list of Avery label (and other vendors) paper styles, after which Access correctly creates a report design based on the data you specify to create your label. After the label is created, you can open the report in Design mode and customize it as needed.
Figure 9-3
An invoice report (rptInvoice)
Figure 9-4
rptCustomerMailingLabels, a typical mailing-label report
Distinguishing between reports and forms
The main difference between reports and forms is the purpose of the output. Whereas forms are primarily for data entry and interaction with the users, reports are for viewing data (either on-screen or in hard copy form). Calculated fields can be used with forms to display an amount based on other fields in the record. With reports, you typically perform calculations on a group of records, a page of records, or all the records processed during the report. Anything you can do with a form—except input data—can be duplicated by a report. In fact, you can save a form as a report and then customize the form controls in the Report Design window.
Understanding the process of creating a report
Planning a report begins long before you actually create the report design. The report process begins with your desire to view your data in a table, but in a way that differs from datasheet display. You begin with a design for this view; Access begins with raw data. The purpose of the report is to transform the raw data into a meaningful set of information. The process of creating a report involves several steps:
• Defining the report layout
• Assembling the data
• Creating the report design using the Access Report Design window
• Printing or viewing the report
Defining the report layout
You should begin by having a general idea of the layout of your report. You can define the layout in your mind, on paper, or interactively using the Access Report Designer. Good reports can first be laid out on paper, showing the controls needed and the placement of the controls. Very often, an Access report is expected to duplicate an existing paper report used by the application’s consumers.
Assembling the data
After you have a general idea of the report layout, you should assemble the data needed for the report. Access reports use data from two primary sources: a single database table, or a recordset produced by the query. You can join many tables in a query and use the query’s recordset as the record source for your report. A query’s recordset appears to an Access report as if it were a single table.
As you learned earlier in this book, you specify the fields, records, and sort order of the records in a query. Access treats this recordset data as a single table (for processing purposes) in datasheets, forms, and reports. The recordset becomes the source of data for the report and Access processes each record to create the report. When the report is run, Access matches data from the recordset or table against the fields specified in the report and uses the data available at that moment to produce the report.
In this example, you use data from tblProducts to create a relatively simple tabular report.
Creating a Report with Report Wizards
Access enables you to create virtually any type of report. Some reports, however, are easier to create than others, especially when a Report Wizard is used as a starting point. Like Form Wizards, Report Wizards give you a basic layout for your report, which you can then customize.
Report Wizards simplify the layout process of your controls by visually stepping you through a series of questions about the type of report that you want to create and then automatically creating the report for you. In this chapter, you use Report Wizards to create tabular and columnar reports.
Creating a new report
The Access ribbon contains several commands for creating new reports for your applications. That Create tab of the ribbon includes a grouping called Reports containing several options such as Report, Labels, and Report Wizard. For this exercise, use the Report Wizard button to create a new report from tblProducts. Begin by clicking the Report Wizard button in the Reports group of the Create ribbon tab. The Report Wizard dialog opens, as shown in Figure 9-5.
Figure 9-5
The first screen of the Report Wizard after selecting a data source and fields
In Figure 9-5, tblProducts has been selected as the data source for the new report. Under the data source selection drop-down list is a list of available fields. Clicking on a field in this list and pressing the right pointing arrow moves the field from the Available Fields list to the Selected Field list, adding it to the report. For this exercise, select Product ID, Description, QtyInStock, RetailPrice, and SalePrice.
Double-click any field in the Available Fields list to add it to the Selected Fields list. You can also double-click any field in the Selected Fields list to remove it from the box. Access then moves the field back to the Available Fields list.
You are limited to selecting fields from the original record source you started with. You can select fields from other tables or queries by using the Tables/Queries drop-down list in the Report Wizard. As long as you have specified valid relationships so that Access properly links the data, these fields are added to your original selection and you use them on the report. If you choose fields from unrelated tables, a dialog box asks you to edit the relationship and join the tables. Or, you can return to the Report Wizard and remove the fields.
After you have selected your data, click the Next button to go to the next wizard dialog box.
Selecting the grouping levels
The next dialog box enables you to choose which field(s) to use for grouping data. Figure 9-6 shows the Category field selected as the data grouping field for the report. The field selected for grouping determines how data appears on the report, and the grouping fields appear as group headers and footers in the report. Groups are most often used to combine data that are logically related. For instance, you may choose to group on CustomerID so that each customer’s sales history appears as a group on the report. You use the report’s group headers and footers to display the customer name and any other information specific to each customer.
The Report Wizard lets you specify as many as four group fields for your report. You use the Priority buttons to change the grouping order on the report. The order you select for the group fields is the order of the grouping hierarchy.
Select the Category field as the grouping field and click (>) to specify a grouping based on category values. Notice that the picture changes to show Category as a grouping field, as shown in Figure 9-6. Each of the other fields (ProductID, Description, QtyInStock, RetailPrice, and SalesPrice) selected for the report will appear within the Category groups.
Figure 9-6
Specifying the report’s grouping
Defining the group data
After you select the group field(s), click the Grouping Options button at the bottom of the dialog box to display another dialog box, which enables you to further define how your report uses the group field.
For instance, you can choose to group by only the first character of a field chosen for grouping. This means that all records with the same first character in the grouping field are included as a single group. If you group a customers table by the CustomerName, and specify to group on the first character of the CustomerName field, a group header and footer appears for the set of all customers whose name begins with the same character. There would be a group for all records with a CustomerName beginning with the letter A, another group for all records with CustomerName beginning with the letter B, and so on.
The Grouping Options dialog box, which is displayed when you click the Grouping Options button in the lower-left corner of the Report Wizard screen, enables you to further define the grouping. This selection can vary in importance, depending on the data type.
The Grouping intervals list box displays different values for the various data types:
• Text: Normal, 1st Letter, 2 Initial Letters, 3 Initial Letters, 4 Initial Letters, 5 Initial letters
• Numeric: Normal, 10s, 50s, 100s, 500s, 1000s, 5000s, 10000s, 50000s, 100000s.
• Date: Normal, Year, Quarter, Month, Week, Day, Hour, Minute.
Normal means that the grouping is on the entire field. In this example, use the entire Customer Name field.
In this example, the default text-field grouping option of Normal is acceptable.
If you displayed the Grouping Options dialog box, click the OK button to return to the Grouping levels dialog box.
Click the Next button to move to the Sort order dialog box.
Selecting the sort order
By default, Access automatically sorts the grouped records in an order that helps the grouping make sense. For instance, after you have chosen the Customer Name field to group customer records, Access arranges the groups in alphabetical order by the CustomerName. However, for your purposes, it may be useful to specify a sort within each group. As an example, your users may want to see the customer records sorted by Order Date in descending order so that the newest orders appear near the top of for each customer group.
In our example, Access sorts data by the Category field. As Figure 9-7 shows, the data is also sorted by Description within each group.
The sort fields are selected by the same method you use for grouping fields in the report. You can select fields that you have not already chosen to group and use these as sorting fields. The fields chosen in this dialog box do not affect grouping. Instead, they affect only the sorting order in the Detail section fields. You select ascending or descending sort by clicking the button to the right of each sort field.
Figure 9-7
Selecting the field sorting order
Selecting summary options
At the bottom of the sorting dialog box is a Summary Options button. Clicking this button displays the dialog box shown in Figure 9-8. This dialog box provides additional display options for numeric fields. As you can see in Figure 9-8, all of the numeric and currency fields are displayed and specified to be summed. Additionally, you can display averages, minimums, and maximums.
Figure 9-8
Selecting the summary options
You can also decide whether to show or hide the data in the Detail section. If you select Detail and Summary, the report shows the detail data, whereas selecting Summary Only hides the Detail section and shows only totals in the report.
Finally, checking the Calculate percent of total for sums box adds the percentage of the entire report that the total represents below the total in the group footer. If, for example, you have three products and their totals are 15, 25, and 10, respectively, 30%, 50%, and 20% shows below their total (that is, 50)—indicating the percentage of the total sum (100%) represented by their sum.
Clicking the OK button in this dialog box returns you to the Sorting dialog box. There you can click the Next button to move to the next wizard dialog box.
Selecting the layout
Two more dialog boxes affect the look of your report. The first (shown in Figure 9-9) enables you to determine the basic layout of the data. The Layout area provides six layout choices that tell Access whether to repeat the column headers, indent each grouping, and add lines or boxes between the detail lines. As you select each option, the picture on the left changes to show how the choice affects the report’s appearance.
You choose between Portrait (up-and-down) and Landscape (across-the-page) layout for the report in the Orientation area. Finally, the Adjust the field width so all fields fit on a page check box enables you to cram a lot of data into a little area. (A magnifying glass may be necessary!)
For this example, choose Stepped and Landscape, as shown in Figure 9-9. Then click the Next button to move to the next dialog box.
Figure 9-9
Selecting the page layout
Choosing the style
After you choose the layout, select the style of your report from the dialog shown in Figure 9-10. Each style has different background shadings, font size, typeface, and other formatting. As each is selected, the picture on the left changes to show a preview. For this example, choose Opulent. Finally, click the Next button to move to the last dialog box.
Figure 9-10
Choosing the style of your report
You can customize the styles, or add your own with the AutoFormat option in the Arrange tab of the Access ribbon with a report open in Design view.
Opening the report design
The final Report Wizard dialog box contains a checkered flag, which lets you know that you’re at the finish line. The first part of the dialog box enables you to enter a title for the report. This title appears only once, at the very beginning of the report, not at the top of each page. The report title also serves as the new report’s name. The default titles is the name of the table or query you initially specified as the report’s data source. The report just created in Chapter09.accdb is named rptProducts_Wizard.
Next, choose one of the option buttons at the bottom of the dialog box:
• Preview the report
• Modify the report’s design
For this example, leave the default selection intact to preview the report. Clicking the Finish button displays the report in Report view. Click Finish to complete the Report Wizard and view the report (see Figure 9-11).
Figure 9-11
rptProducts_Wizard displayed in Report View
Report view provides an overall view of the report, but it does not show the margins, page numbering, and how the report will look when printed on a piece of paper. To get a good idea of how a report will look when printed, right-click the report’s title bar and select Print Preview from the shortcut menu that appears.
Using the Print Preview window
Figure 9-12 shows the Print Preview window in a zoomed view of rptProducts_Wizard. This view displays your report with the actual fonts, shading, lines, boxes, and data that will be on the printed report. Clicking the left mouse button changes the view to a page preview that shows the entire page.
The Access ribbon transforms to display controls relevant to viewing and printing the report. The Print Preview tab of the Access ribbon includes controls for adjusting the size, page orientation (Portrait or Landscape), and other viewing options. The Print Preview tab also includes a handy Print button for printing the report.
You can move around the page by using the horizontal and vertical scrollbars. Use the Page controls (at the bottom-left corner of the window) to move from page to page. These controls include VCR-like navigation buttons to move from page to page or to the first or last page of the report. You can also go to a specific page of the report by entering a value in the text box between the previous and next controls.
Right-clicking on the report and selecting the Multiple Pages option lets you view more than one page of the report in a single view. Figure 9-13 shows a view of the report in the Print Preview’s multipage mode. Use the navigation buttons (in the lower-left section of the Print Preview window) to move between pages, just as you would to move between records in a datasheet. The Print Preview window has a toolbar with commonly used printing commands.
Figure 9-12
Displaying rptReport_Wizard in the zoomed preview mode
Figure 9-13
Displaying multiple pages of a report in Print Preview’s page preview mode
If, after examining the preview, you are satisfied with the report, click the Printer button on the toolbar to print the report. If you are dissatisfied, select the Close button to switch to the Report Design window and make further changes.
Viewing the Report Design window
Right-clicking the report’s title bar and selecting Design View opens the Access Report Designer on the report. As shown in Figure 9-14, the report design reflects the choices you made using the Report Wizard.
Figure 9-14
The Report Design window
Return to the Print Preview mode by selecting the Print Preview button on the Report Design toolbar or by selecting the Print Preview option from the File menu. You can also select Print or Page Setup from the File menu. This menu also provides options for saving your report.
Printing a Report
There are several ways to print your report:
• Click the Print button in the Print Preview tab of the Access ribbon.
• Click File⇒Print in the main Access window (with a report highlighted and the Navigation Pane).
Selecting File⇒Print opens the standard Windows Print dialog box. You use this dialog to select the print range, number of copies, and print properties.
Clicking the Print button in the Access ribbon immediately sends the report to the default printer without displaying a Print dialog box.
Saving the Report
Save the report design at any time by selecting File⇒Save, File⇒Save As, or File⇒Export from the Report Design window, or by clicking the Save button on the Quick Access Toolbar. The first time you save a report (or any time you select Save As or Export), a dialog box enables you to select or type a name.
Starting with a Blank Form
Previous chapters about forms introduced you to all the tools available in the Report Design window. When you create reports, you use some of these tools in a slightly different manner from the way you used them to create forms. Therefore, it is important to review some of the unique report menus and toolbar buttons.
You can view a report in four different views: Design, Report, Layout, and Print Preview. You can also print a report to the default Windows printer. You have already seen various preview windows in previous chapters. This chapter focuses on the Report Design window.
Layout view
The Report Design window is one place where you create and modify reports. You began working with a new report by selecting a table or query to serve as the new report’s data source; then you click the Blank Report button in the Create tab of the main Access ribbon. The new report appears in Layout view as shown in Figure 9-15.
Figure 9-15
Layout view of a new report based on tblProducts
The ability to change a report’s design in Layout view is a new feature of Access 2007. The main advantage of Layout view is that you can see the relative positions of the controls on the report’s surface, as well as the margins, page headers and footers, and other report details.
The main constraint of Layout view is that you cannot make fine adjustments to a report’s design unless you put the report in Design view. Layout view is primarily intended to allow you to adjust the relative positions of controls on the report. For instance, the icon that appears in the upper-left corner of the report shown in Figure 9-15 can be deleted by clicking on the icon and pressing the Delete button, or moved to another location by dragging it to a better location on the report’s surface.
While in Layout view, you can also right-click any control and select Properties from the shortcut menu. The Property Sheet allows you to modify the default settings for the selected control.
Figure 9-16 shows the Access ribbon while a report is open in Layout view. Not surprisingly, the options on the ribbon are mostly involved with adjusting the appearance of the controls on the report.
Figure 9-16
The Access ribbon while a report is open in Layout view
In Figure 9-16 notice that you cannot adjust the fine details of a control, such as its height or width, but you can adjust the font used for the control, the font size, the BackColor, and the ForeColor of a report control. To adjust a control’s height and width, click on the control and drag its margins to the new height or width.
Report Design view
As an alternative to the Layout view, you may choose to use the more traditional Report Design view, which gives you a high level of control over the controls on a report, as well as the report itself. Right-click the report in Layout view, and select Design view from the shortcut menu to open the report in the traditional Access Report Designer (see Figure 9-17).
Figure 9-17
The new report open in Report Design view
The Report Design ribbon is shown in Figure 9-18. Notice how much more complex the ribbon is when the report is in Design view than when it is open in Layout view. You have many more options for influencing how a report looks on the printed page in Design view than in Layout view.
Figure 9-18
The Report Design ribbon
Banded Report Writer Concepts
In a report, your data is processed one record at a time. Depending on how you create your report design, each data item is processed differently. Reports are divided into sections, known as bands in most report-writing software packages. (In Access, these are simply called sections.) Access processes each record in the underlying data set, processing each section in order and deciding (for each record) whether to process fields or text in that section. For example, the report footer section is processed only after the last record is processed in the recordset.
In Figure 9-19 (rptProductsSummary) notice that the data on the report is grouped by ProductCategory (Minivans, Motor Homes, and so on). Each group has a group header containing the category name (The first category in this example is Minivans.) Each group also has a footer displaying summary information for the category. In Figure 9-19, the total profit is $17,063 on total sales of $93,063. The page header contains column descriptions (Product ID, Description, and so on), whereas the report header contains the report title (Products Summary). Finally, the report footer contains grand totals for the report, and the page footer shows the page number.
Figure 9-19
rptProductsSummary, a grouped report containing summary data
The following Access sections are available:
• Report header: Prints only at the beginning of the report; used for title page.
• Page header: Prints at the top of each page.
• Group header: Prints before the first record of a group is processed.
• Detail: Prints each record in the table or recordset.
• Group footer: Prints after the last record of a group is processed.
• Page Footer: Prints at the bottom of each page.
• Report footer: Prints only at the end of a report after all records are processed.
The Report Designer sections
Figure 9-20 shows rptProductSummary open in Design view. As you can see, the report is divided into seven sections. The group section displays data grouped by categories, so you see the sections Category Header and Category Footer. Each of the other sections is also named for the type of processing it performs.
You can place any type of text or text box controls in any section, but Access processes the data one record at a time. It also takes certain actions (based on the values of the group fields, the location of the page, or placement in the report) to make the bands or sections active. The example in Figure 9-20 is typical of a report with multiple sections. As you learned, each section in the report has a different purpose and different triggers.
Figure 9-20
The Report Design window
Page and report headers and footers are added as pairs. To add one without the other, resize the section you don’t want to a height of zero or set its Visible property to No.
If you remove a header or footer section, you also lose the controls in those sections.
The Report Header section
Controls in the Report Header section are printed only once at the beginning of the report. A common use of a Report Header section is as a cover page or a cover letter or for information that needs to be communicated only once to the user of the report.
You can also have controls in the Report Header section print on a separate page, which enables you to create a title page and include a graphic or picture in the section. There is a Force New Page property in the Report Header section that can be set to After Section that will place the information in the report header into a separate page.
In Figure 9-20 the Report Header section is not used. Notice that the Report Header’s height is zero.
Only data from the first record can be placed in a report header.
The Page Header section
Text or text box controls in the Page Header section normally print at the top of every page. If a report header on the first page is not on a page of its own, the information in the Page Header section prints just below the report header information on the first page. Typically, page headers serve as column headers in group/total reports; they can also contain a title for the report. In this example, placing the Products Summary report title in the Page Header section means that the title appears on every page.
The page header section shown in Figure 9-20 contains horizontal lines above and below the label controls. Each label control can be moved or sized individually. You can also change special effects (such as color, shading, borders, line thickness, font type, and font size) for each control.
Both the Page Header and Page Footer sections can be set to one of four settings (found in the Report’s properties, not the section properties):
• All Pages: The page header and page footer print on every page.
• Not with Report Header: Neither the page header nor footer prints on a page with the report header.
• Not with Report Footer: The page header does not print with the report footer. The report footer prints on a new page.
• Not with Report Header/Footer: Neither the page header nor the footer prints on a page with the report header or footer.
The Group Header section
A Group Header section normally displays the name of the group, such as “Minivans” or “Motor Homes.” Access knows when all the records in a group have been displayed in a Detail section when the group name changes. In this example, the detail records are all about individual products. The Category control in the Category Header tells you that the products within the group belong to the indicated category (Minivan or Motor Home). Group headers immediately precede Detail sections.
It is possible to have multiple levels of group headers and footers. In this report, for example, the data is only for categories. However, in some reports you might have groups of information with date values. You could group your sections by year or month and year, and within those sections by another group such as category.
To set group-level properties such as Group On, Group Interval, Keep Together, or something other than the default, you must first set the Group Header and Group Footer property (or both) to Yes for the selected field or expression. You learn about these later in the chapter.
The Detail section
The Detail section processes every record in the data and is where each value is printed. The Detail section frequently contains a calculated field such as profit that is the result of a mathematical expression. In this example, the Detail section simply displays information from the tblProduct table except for the last control. The profit is calculated by subtracting the cost from the SalePrice.
You can tell Access whether you want to display a section in the report by changing the section’s Visible property in the Report Design window. Turning off the display of the Detail section (or by excluding selected group sections) displays a summary report with no detail or with only certain groups displayed.
The Group Footer section
You use the Group Footer section to calculate summaries for all the detail records in a group. In the Products Summary report, the expression = Sum([SalePrice] - [Cost]) adds a value calculated from all of the records within a category. In the Minivans group, this expression sums seven records. The value of this text box control is automatically reset to 0 every time the group changes. (You learn more about expressions and summary text boxes in later chapters.)
You can change the way summaries are calculated by changing the Running Sum property of the text box in the Report Design window.
The Page Footer section
The Page Footer section usually contains page numbers or control totals. In very large reports, such as when you have multiple pages of detail records with no summaries, you may want page totals as well as group totals. For the Products Summary Report, the page number is printed by combining the text page, and built-in page number controls. These controls show Page x of y where x is the current page number and y is the total number of pages in the report. A text box control with the following expression in the Control Source property can be used to display page number information that keeps track of the page number in the report:
=”Page: “ & [Page] & “ of “ & [Pages]
You can also print the date and the time printed. You can see the page number text box in the Page Footer section in Figure 9-20. The Page Footer in rptProductsSummary also contains the current date and time.
The Report Footer section
The Report Footer section is printed once at the end of the report after all the detail records and group footer sections are printed. Report footers typically display grand totals or other statistics (such as averages or percentages) for the entire report. The report footer for the Products Summary report uses the expression = Sum with each of the numeric fields to sum the amounts.
When there is a report footer, the Page Footer section is printed after the report footer.
The Report Writer in Access is a two-pass report writer, capable of preprocessing all records to calculate the totals (such as percentages) needed for statistical reporting. This capability enables you to create expressions that calculate percentages as Access processes those records that require foreknowledge of the grand total.
Creating a Report from Scratch
Fundamental to all reports is the concept that a report is another way to view the records in one or more tables. It is important to understand that a report is bound to either a single table or a query that brings together data from one or more tables. When you create a report, you must select which fields from the query or table you want to see in your report. Unless you want to view all the records from a single table, bind your report to a query. Even if you are accessing data from a single table, using a query lets you create your report on the basis of a particular search criterion and sorting order. If you want to access data from multiple tables, you have almost no choice but to bind your report to a query. In the examples in this chapter, all the reports are bound to a query (even though it is possible to bind a report to a table).
Access lets you create a report without first binding it to a table or query, but you will have no controls on the report. This capability can be used to work out page templates with common text headers or footers such as page numbering or the date and time, which can serve as models for other reports. You can add controls later by changing the underlying control source of the report.
Throughout the rest of this chapter, you learn the tasks necessary to create the Products Display Report (a part of the first page is shown in Figure 9-21). In these sections, you design the basic report, assemble the data, and place the data in the proper positions.
Figure 9-21
The Products Summary report
As with almost every task in Access, there are many ways to create a report without wizards. It is important, however, to follow some type of methodology, because creating a good report involves a fairly scientific approach. You should create a check list that is a set of tasks that will result in a good report every time. As you complete each task, check it off your list. When you are done, you will have a great-looking report. The following section outlines this approach.
Creating a new report and binding it to a query
The first step is to create a new, empty report and bind it to tblProducts. Creating a blank report is quite easy:
1. Select the Create tab of the main Access ribbon.
2. Click the Blank Report button in the Reports ribbon group.
Access opens a blank report in Layout view, and positions a Field List dialog on top of the new report (see Figure 9-22).
Figure 9-22
A blank report in Layout view
At this point, you have two different paths for adding controls to the report: continue working in Layout view, or switch to Design view. Each of these techniques has advantages over the other, but for the purposes of this exercise we’ll use the Design view because it better demonstrates the process of building Access reports.
Right-click the report’s title bar, and select Design view from the shortcut menu. The Report window transforms to the traditional Access banded Report Designer, as shown in Figure 9-23. This figure also shows the Field List open on tblProducts, allowing you to track fields from the list to the appropriate section on the new report.
Figure 9-23
Building the new report in Design view
In Figure 9-23, the Description field has been dragged onto the Detail section of the report.
Defining the report page size and layout
As you plan your report, consider the page-layout characteristics as well as the kind of paper and printer you want to use for the output. As you make these decisions, you use several dialog boxes and properties to make adjustments. These specifications work together to create the desired output.
Click the Page Setup tab in the Access ribbon to select the report’s margins, orientation, and other overall characteristics. Figure 9-24 shows a portion of the Access screen with the Page Setup tab selected, and the Margins option open.
Figure 9-24
The Page Setup dialog box showing the Page tab
Notice that the Page Setup tab contains options for setting the paper size, the report’s orientation (Portrait or Landscape), its margins, and other details. Dropping down either the Size or Margins option reveals a tab containing common settings for each of these options.
rptProductDisplay is to be a portrait report, which is taller than it is wide. You want to print on letter size paper (8 1/2 x 11 inches), and you want the left, right, top, and bottom margins all set to 0.25 inches. In Figure 9-24 notice that the Narrow margins option is selected, which specifies exactly 0.25 inches for all four margin settings.
If the margins you need for your particular report are not shown in the margins tab, click the small button in the lower-right corner of the page layout group to open the common Windows Page Setup dialog. This dialog enables you to specify the margins, orientation, and other page layout specifications as you would in Microsoft Word or any other Windows application.
To set the right border for the Product Display report to 7 1/2 inches, follow these steps:
1. Click the right edge of the report body (where the white page meets the gray background).
The mouse pointer changes to a double-headed arrow.
2. Drag the edge to the 7 1/2-inch mark.
If the ruler is not displayed in the Report Designer, select the Arrange tab, move to the Show/Hide group, and click the ruler icon.
You can also change the Width property in the Property window for the report.
If you run your report and every other page is blank, it is a sign that the width of your report exceeds the width of your page. To fix this problem, decrease your left and right margin size or reduce the report’s width. Sometimes, when you move controls around, you accidentally make the report width larger than you originally intended. For example, in a portrait report, if your left margin + report width + right margin is greater than 8 1/2 inches, you will see blank pages.
Placing controls on the report
Access takes full advantage of drag-and-drop capabilities of Windows. The method for placing controls on a report is no exception:
1. Click the Add Existing Fields button in the Tools group of the Design ribbon tab.
The Field List window appears.
2. Click the desired Toolbox control to determine the type of control to create if they are to be different from the default control types for the fields.
3. Select each field that you want on your report and then drag them to the appropriate section of the Report Design window.
Select multiple fields by holding down the Ctrl key as you click on fields in the Field List. Depending on whether you choose one or several fields, the mouse pointer changes shape to represent your selection as you drag fields on to the report.
The fields appear in the detail section of the report, as shown in Figure 9-25. Notice that for each field you dragged onto the report, there are two controls. When you use the drag-and-drop method of placing fields, Access automatically creates a label control with the field name attached to the Text control to which the field is bound.
Figure 9-25
The report with several fields added
Notice the Bound Object Frame control for the field named Picture. Access always creates a Bound Object Frame control for an OLE-type object found in a table. Also notice that the Detail section automatically resizes itself to fit all the controls. Above the Bound Object Frame control is the control for the memo field Features.
Controls are needed for the customer information in the page header section. Before you do this, however, you must resize the page header to leave room for a title you will add later.
Resizing a section
To make room on the report for the title information in the page header, you must resize it. You resize by using the mouse to drag the bottom of the section you want to resize. The mouse pointer turns into a vertical double-headed arrow as it is positioned over the bottom of a report section. Simply drag the section border up or down to make the section smaller or larger.
Resize the Page Header section to make it about 3/4-inches high by dragging the bottom margin of the page header downwards. Use the Controls group on the Design ribbon tab to drag labels to the report. Add two labels to the Page Header, and enter Product Display as the Caption property of one label, and Access Auto Auctions to the other.
The labels you just added are unattached; they are not related to any other controls on the report. When you drag a field from the Field List Access adds not only a text box to contain the field’s data, but also a label to provide an identifier for the text box. Labels that you drag from the Controls group on the Access ribbon are unattached and are not related to text boxes or any other control on the report.
You may notice the Page Header section expanding to accommodate the label controls that you dragged into the section. All the fields needed for the Product Display report are now placed in their appropriate sections.
To create a multiple-line label entry, press Ctrl+Enter to force a line break where you want it in the control.
If you enter a caption that is longer than the space in the Property window, the contents scroll as you type. Otherwise, open a Zoom box that gives you more space to type by pressing Shift+F2.
Modifying the appearance of text in a control
To modify the appearance of the text in a control, select the control by clicking its border (not in the control itself). You can then select a formatting style to apply to the label by clicking the appropriate button on the Formatting toolbar.
To make the titles stand out, follow these steps to modify the appearance of label text:
1. Click the newly created report heading label Product Display.
2. Click the Bold button in Font group on the ribbon.
3. Click the arrow beside the FontSize drop-down box and select 18 from the drop-down list.
4. Repeat for the Access Auto Auctions label, using a 12 pt font and Bold.
The size of the labels may not fit their displayed text. To tighten the display or to display all the text when a label isn’t big enough, double-click any of the sizing handles, and Access chooses an appropriate size for the label.
Figure 9-26 shows these labels added, resized, and formatted in the report’s Page Header section.
Figure 9-26
Adding unbound labels to the report
Working with text boxes and their attached label controls
So far, you added controls bound to fields in the tables and unbound label controls used to display titles in your report. There is another type of text box control that is typically added to a report: unbound text boxes that are used to hold expressions such as page numbers, dates, or a calculation.
Adding and using text box controls
In reports, text box controls serve two purposes. First, they enable you to display stored data from a particular field in a query or table. Second, they display the result of an expression. Expressions can be calculations that use other controls as their operands, calculations that use Access functions (either built-in or user-defined), or a combination of the two. You learned how to use a text box control to display data from a field and how to create that control.
Entering an expression in a text control
Expressions enable you to create a value that is not already in a table or query. They can range from simple functions (such as a page number) to complex mathematical computations.
Chapter 5 discusses expressions in greater detail.
A function is a small program that, when run, returns a single value. The function can be one of many built-in Access functions or it can be user-defined. For example, earlier in this chapter you saw the Now() function used to return the current date and time for a text box located in a report’s page footer. The following steps show you how to use an unbound text box to add a page number to your report:
1. Click in the middle of the Page Footer section, resize the page footer so that it is a 1/2 inch in height
2. Drag a text box control from the Controls group on the Design ribbon tab and drop it into the Page Footer area. Make the text box about three-quarters of the height of the Page Footer section and about 1/2-inch wide.
3. Select the text box’s attached label and change its contents to say Page:.
4. Select the text box control (it says “Unbound”) and enter = Page directly into the text box.
Alternatively, you could open the Property sheet (press F4) and enter = [Page] as the text box’s ControlSource property.
5. Drag the new text box control until it is near the right edge of the report’s page (see Figure 9-27).
You may want to also move the text box’s label so that it is positioned close to the text box. The upper-left handle on the label moves the label independently of the text box.
Figure 9-27
Adding a page-number expression in a text box control
You can always check your result by clicking the Print Preview button on the toolbar and zooming in on the Page Footer section to check the page number.
Sizing a text box control or label control
You select a control by clicking it. Depending on the size of the control, from three to seven sizing handles appear—one on each corner except the upper-left corner and one on each side. Moving the mouse pointer over one of the sizing handles changes the mouse pointer to a double-headed arrow. When the pointer changes, click the control and drag it to the size you want. Notice that, as you drag, an outline appears indicating the size the label control will be when you release the mouse button.
If you double-click any of the sizing handles, Access resizes a control to best fit for text in the control. This feature is especially handy if you increase the font size and then notice that the text no longer fits the control.
Note that, for label controls, the best-fit sizing resizes both vertically and horizontally, although text controls resize only vertically. The reason for this difference is that in Report Design mode, Access doesn’t know how much of a field’s data you want to display. Later on, the field’s name and contents might be radically different. Sometimes label controls are not resized correctly, however, and have to be adjusted manually.
You can also select Format⇒Size⇒To Fit to change the size of the label control text automatically.
Before continuing, you should check how the report is progressing. You should also save the report frequently as you make changes to it. You could send a single page to the printer, but is probably easier to view the report in Print Preview. Right-click the report’s title bar, and select Print Preview from the shortcut menu. Figure 9-28 shows a zoomed print preview of the report’s current appearance. The page header information is at the very top of the page, and the first product record appears below the header.
Figure 9-28
A print preview of the report
As you move the mouse over the print preview, the cursor changes to a magnifying glass. Click any portion of the view to zoom in so that can closely examine the report’s layout. Only one record per page appears on the report because of the vertical layout. In the next section, you move the controls around and create a more horizontal layout.
Deleting and cutting attached labels from text controls
To create the report shown in Figure 9-19, you must move the text box labels from the Detail section to the Page Header section. Once moved, these controls appear as headings above each column of data and are repeated on each page of the report.
It’s easy to delete one or more attached controls in a report. Simply select the desired controls and press Delete. However, if you want to move the label to the Page Header section (rather than simply deleting it), you can cut the label instead of deleting it. When removing attached controls, there are two choices:
• Delete only the label control.
• Cut the label control to the Clipboard.
• Delete or cut the label and the text box control.
Oddly enough, you cannot simply drag a label from the Detail section to the page header. Dragging an attached label from the Detail section drags its text box along with it. You must cut the label from the Detail section and paste it into the page header.
If you select the label control and cut it by pressing Ctrl+X or the Delete key, only the label control is removed. If you select the text box control and cut or delete it, the label and the text box controls are removed. To cut an attached label control (in this case, the Product ID controls and their attached label), follow these steps:
1. Click the Close button on the toolbar to exit Print Preview mode.
2. Select the Product ID label control only in the Detail section.
3. Press Ctrl+X (Cut).
After you have cut the label, you may want to place it somewhere else. In this example, place it in the Page Header section.
Pasting labels into a report section
It is as easy to cut labels from controls placed in the Detail section and paste them into the page header as it is to delete the labels and create new ones in the page header. Regardless, you now paste the label you cut in the previous steps:
1. Click anywhere in or on the Page Header section.
2. Press Ctrl+V (Paste).
The Product ID label appears in the page header.
3. Repeat for the Description, Category, and Quantity in Stock labels.
4. Delete the remaining label controls in the Detail section, leaving all the text box controls.
If you accidentally selected the data text box control and both controls are cut or deleted, click the Undo toolbar button, or press Ctrl+Z, to undo the action.
If you want to delete only the text box control and keep the attached label control, right-click the label control and then select Copy from the shortcut menu. Next, to delete the text box control and the label control, press the Delete key. Finally, right-click anywhere on the form and select Paste from the shortcut menu to paste only the copied label control to the report.
Moving label and text controls
Before discussing how to move label and text controls, it is important to review a few differences between attached and unattached controls. When an attached label is created automatically with a text control, it is called a compound control. In a compound control, whenever one control in the set is moved, the other control moves along with it. This means that, moving either the label or the text box also moves the related control.
To move both controls in a compound control, select either of the pair of controls with the mouse. As you move the mouse pointer over either of the objects the pointer turns into a hand. Click the controls and drag them to their new location. As you drag, an outline for the compound control moves with your pointer.
To move only one of the controls in a compound control, drag the desired control by its move handle (the large square in the upper-left corner of the control). When you click a compound control, it looks like both controls are selected, but if you look closely, you see that only one of the two controls is selected (as indicated by the presence of both moving and sizing handles). The unselected control displays only a moving handle. A pointing finger indicates that you have selected the move handles and can now move only one control. To move either control individually, select the control’s move handle and drag it to its new location.
To move a label that is not attached, simply click any border (except where there is a handle) and drag it.
To make a group selection, click with the mouse pointer anywhere outside a starting point and drag the pointer through (or around) the controls you want to select. A gray, outlined rectangle appears, showing the extent of the selection. When you release the mouse button, all controls the rectangle surrounds are selected. You can then drag the group of controls to a new location.
The global option File⇒Access Options⇒Object Designers⇒Forms/Reports ⇒Selection Behavior is a property that controls the enclosure of selections. You can enclose them fully (the rectangle must completely surround the selection) or partially (the rectangle must touch only the control), which is the default.
Make sure you also resize all the controls as shown in the figure. Change the size and shape of the Features memo field and the OLE picture field Picture. The OLE picture field displays as a rectangle with no field name in design view. It is to the right in Figure 9-29.
Place all the controls in their proper position to complete the report layout. You the controls arranged as shown in the example in Figure 9-29. You make a series of block moves by selecting several controls and positioning them close to where you want them. Then, if needed, you fine-tune their position. This is the way most reports are done.
Follow Figure 9-29 to begin placing the controls where they should be. Notice the Cost label in the Detail section has been renamed to Cost/Retail/Sale Prices.
Figure 9-29
Rearranging the controls on the report
At this point, you’re about halfway done. The screen should look like the one shown in Figure 9-29. (If it doesn’t, adjust your controls until your screen matches the figure.) Remember that these screen pictures are taken with Windows set to 1024 x 768. If you are using a lower resolution, or have large fonts turned on the Windows Display Properties (in Control Panel), you have to scroll the screen to see the entire report.
These steps complete the rough design for this report. There are still properties, fonts, and sizes to change. When you make these changes, you have to move controls around again. Use the designs in Figure 9-29 only as a guideline. How it looks to you, as you refine the look of the report in the Report window, determines the final design.
Modifying the appearance of multiple controls
The next step is to apply bold formatting to all the label controls in the Page Header section directly above the section separator. The following steps guide you through modifying the appearance of text in multiple label controls:
1. Select all label controls in the bottom of the Page Header section by individually clicking them while holding down the Shift key.
Alternatively, click in the vertical ruler immediately to the left of the labels in the Page Header. There are four label controls to select, as shown in Figure 9-29.
Alternatively, you can drag a bounding box around the label controls in the page header.
2. Click the Bold button on the toolbar.
After you make the final modifications, you are finished, except for fixing the picture control. To do this, you need to change properties, which you do in the next section.
This may seem to be an enormous number of steps because the procedures were designed to show you how laying out a report design can be a slow process. Remember, however, that when you click away with the mouse, you don’t realize how many steps you are doing as you design the report layout visually. With a WYSIWYG (What You See Is What You Get) layout like an Access report, you may need to perform many tasks, but it’s still easier and faster than programming. Figure 9-29 shows the final version of the design layout as seen in this chapter. In the next chapter, you continue to improve this report layout.
Changing label and text box control properties
To change the properties of a text or label control, you need to display the control’s Property Sheet. If it is not already displayed, perform one of these actions to display it:
• Double-click the border of the control (anywhere except a sizing handle or move handle).
• Press F4.
• Right-click the mouse and select Properties.
The Property Sheet enables you to look at a control’s property settings and provides an easy way to edit them. Using tools, such as the formatting windows and text-formatting buttons, on the Design ribbon also changes the property settings of a control. Clicking the Bold button, for example, sets the control’s Font Weight property to Bold. It is usually easier and more intuitive to use the controls on the Design ribbon, but some properties are not accessible this way. In addition, sometimes objects have more options available through the Property Sheet.
The Size Mode property of an OLE object (bound object frame), with its options of Clip, Stretch, and Zoom, is a good example of a property that is available only through the Property Sheet.
The image control, which is a bound object frame, presently has its Size Mode property set to Clip, which is the default. With Clip, the picture is displayed in its original size and may be too large to fit in the frame. In this exercise, you change the setting to Stretch so that the picture is sized automatically to fit the picture frame.
Chapter 24 covers the use of pictures, OLE objects, and graphs.
Follow these steps to change the property for the bound object frame control that contains the picture:
1. Click the frame control of the picture bound object.
2. Click the Size Mode property and Click the arrow to display the drop-down list box.
3. Select Stretch.
You might also consider changing the Border Style property to Transparent. When set to Transparent, no boxes drawn around the picture on the report.
These steps complete the changes so far to your report. A print preview of the first few records appears in Figure 9-30. If you look at the pictures, notice how the picture is properly displayed and the Features control now appears across the bottom of the Detail section.
Figure 9-30
The report displayed in print preview
Growing and shrinking text box controls
When you print or print-preview controls that can have variable text lengths, Access provides options for enabling a control to grow or shrink vertically, depending on the exact contents of a record. The Can Grow and Can Shrink properties determine whether a text control resizes its vertical dimension to accommodate the amount of text contained in its bound field. Although these properties are usable for any text control, they are especially helpful for text box controls.
Table 9-1 explains the acceptable values for these two properties.
To change the Can Grow settings for a text control, follow these steps:
1. Select the Features text box control.
2. Display the Property window.
3. Click the Can Grow property; then click the arrow and select Yes.
The Can Grow and Can Shrink properties are also available for report sections. Use a section’s Property Sheet to modify these values. Setting a report section’s Can Grow and Can Shrink properties affects only the section, not the controls contained within the section.
The report is starting to look good, but you may want to see groups of like data together and determine specific orders of data. To do this, you use sorting and grouping.
Sorting and grouping data
You can often make the data on the report more useful to users by grouping the data in informative ways. Suppose that you want to list your products first by category and then by description within each category. To do this, you use the Category and Description fields to group and sort the data.
Creating a group header or footer
Grouping on a field in the report’s data adds two new sections (Group Header and Group Footer) to the report. In the following steps, you use the group header to display the name of the product category above each group of records. You won’t use the Category group footer in this example because there are no totals by category or other reasons to use a group footer.
Follow these steps to create a Category group header:
1. Click the Grouping button in the Grouping and Totals group of the Design ribbon tab.
You should see that the report’s data already sorted by Description and Category.
2. Click the Add a group button in the Group, Sort, and Total area.
3. Select Category from the field list, and Access adds Group on Category with A on top of the Group, Sort, and Total area.
Access adds Category Header and Category Footer sections to the report’s design as soon as you select the Category field for grouping. The Category Header section appears between the Page Header and Detail sections. If you define a group footer, it appears below the Detail section. If a report has multiple groupings, each subsequent group becomes the one closest to the Detail section. The groups defined first are farthest from the Detail section.
The Group Properties pane (displayed at the bottom of the Sorting and Grouping box) contains these properties:
• Group Header: Yes creates a group header. No removes the group header.
• Group Footer: Yes creates a group footer. No removes the group footer.
• Group On: Specifies how you want the values grouped. The options you see in the drop-down list box depend on the data type of the field on which you’re grouping. If you group on an expression, you see all grouping options as listed below.
For Text data types, there are two choices:
• Each Value: The same value in the field or expression
• Prefix Characters: The same first n number of characters in the field
For Date/Time data types, there are additional options:
• Each Value: The same value within the field or expression
• Year: Dates within the same calendar year.
• Qtr: Dates within the same calendar quarter
• Month: Dates within the same month
• Week: Dates within the same week
• Day: Dates on the same date
• Hour: Times within the same hour
• Minute: Times within the same minute
Currency, or Number data types provide three options:
• Each Value: Includes the same value in the field or expression
• Interval: Includes values falling within the interval you specify
• Group Interval: Specifies any interval that is valid for the values in the field or expression you’re grouping on. The Group Interval has its own options:
• Keep Together: Controls widows and orphans so that you don’t have a header at the bottom of a page without detail until the next page
• Whole Group: Prints header detail and group footer on one page
• With First Detail: Prevents the contents of the group header from printing without any following data or records on a page
• No: Does not keep data together
Sorting data within groups
Sorting enables you to determine the order in which the records are viewed on the report, based on the values in one or more controls. This order is important when you want to view the data in your tables in a sequence other than that of your input. For example, new products are added to tblProducts as they are needed on an invoice. The physical order of the database reflects the date and time a product is added. Yet, when you think of the product list, you probably expect it to be in alphabetical order by Product ID, and you want to sort it by Description of the cost of the product. By sorting in the report itself, you don’t have to worry about the order of the data. Although you can sort the data in the table by the primary key or in a query by any field you want, there are good reasons to do it in the report. This way, if you change the query or table, the report is still in the correct order.
In the case of the products report, you want to display the records in each category group sorted by description. Follow these steps to define a sort order based on the Description field within the Category grouping:
1. Click the Grouping button in the Design ribbon tab to display the Group, Sort and Total area, if it is not already open.
You should see that the Category group already exists in the report.
2. Click the Add a Sort button in the Group, Sort and Total area.
3. Select Description in the field list. Notice that Sort Order defaults to Ascending.
4. Close the Group, Sort and Total area by clicking the X in the upper-right corner.
The Group, Sort and Total section should now look like Figure 9-31.
Figure 9-31
The Group, Sort and Total area completed
Although in this example you used a field, you can alternatively sort (and group) with an expression. To enter an expression, click the Add a sort or Add a group button in the Group, Sort and Total area and click the Expression button at the bottom of the field list. The Expression Builder dialog opens, enabling you to enter any valid Access expression, such as in = [RetailPrice]-[Cost].
To change the sort order for fields in the Field/Expression column, simply click the drop-down arrow to the right of the button with the A on top to display the Sort Order list. Select Descending from the sort options that appear.
Removing a group header or footer
To remove a Page or Report Header/Footer section, display the Group, Sort and Total area, select the group or sort specifier to delete, and press the Delete key.
Hiding a section
Access also enables you to hide headers and footers so that you can break data into groups without having to view information about the group itself. You can also hide the Detail section so that you see only a summary report. To hide a section, follow these steps:
1. Click the section you want to hide.
2. Display the section’s Property Sheet.
3. Click the Visible property’s text box.
4. Click the drop-down list arrow on the right side of the text box.
5. Select No from the drop-down list box.
Sections are not the only objects in a report that can be hidden; controls also have a Visible property. This property can be useful for expressions that trigger other expressions.
Sizing a section
Now that you have created the group header, you might want to put some controls in the section, move some controls around, or even move controls between sections. Before you start manipulating controls within a section, you should make sure the section is the proper height.
To modify the height of a section, drag the top border of the section below it. If, for example, you have a report with a page header, Detail section, and page footer, change the height of the Detail section by dragging the top of the Page Footer section’s border. You can make a section larger or smaller by dragging the bottom border of the section. For this example, change the height of the group header section to 3/8 inch with these steps:
1. Move your mouse pointer to the bottom of the Category section.
The pointer changes to a horizontal line split by two vertical arrows.
2. Select the top of the detail section (which is also the bottom of the Category Header section).
3. Drag the selected band lower until three dots appear in the vertical ruler (3/8").
The gray line indicates where the top of the border will be when you release the mouse button.
4. Release the mouse button.
Moving controls between sections
You now want to move the Category control from the Detail section to the Category Header section. You can move one or more controls between sections by simply dragging the control with your mouse from one section to another or by cutting it from one section and pasting it to another section. Follow these instructions to move the Category control from the Detail section to the Category section:
1. Select the Category control in the Detail section.
2. Drag the Category control up to the Category Header section and drop it close to the vertical ruler, as shown in Figure 9-32.
You should now perform the following steps to complete the report design.
3. Delete the Category label from the page header.
4. Move the ProductID control and its associated label after the Description control and its associated label, as shown in Figure 9-30.
5. Move the Description control and its associated label to the left so that it starts just to the right of the start of the Category control in the Category Header control.
By offsetting the first control in the Detail section slightly to the right of the start of the control in the Group Header section, you show the hierarchy of the data presented in the report. It now shows that each group of products is for the category listed in the group header.
6. Lengthen the Description control so that it approaches the Product ID control.
Figure 9-32 shows this Property window and the completed report design. The Property sheet is opened in this figure so that you can see how the Force New Page property is set for the Category Header section.
Figure 9-32
Completing the Group Header section and forcing a page break before the Category Header section
Adding page breaks
Access enables you to force page breaks based on groups. You can also insert forced breaks within sections, except in Page Header and Footer sections.
In some report designs, it’s best to have each new group begin on a different page. You can achieve this effect easily by using the Force New Page property of a group section, which enables you to force a page break every time the group value changes.
The four Force New Page property settings are listed here:
• None: No forced page break (the default)
• Before Section: Starts printing the current section at the top of a new page every time there is a new group
• After Section: Starts printing the next section at the top of a new page every time there is a new group
• Before & After: Combines the effects of Before Section and After Section
To create the report you want, force a page break before the Category group with the Force New Page property in the Category header:
1. Click anywhere in the Category header, or click the Category Header bar above the section.
2. Display the Property Sheet.
3. Select the Force New Page property, and select Before Section from the drop-down list in the property’s box.
Alternatively, you can set Force New Page property to After Section in the Category Footer section.
Sometimes, you don’t want to force a page break on the basis of a grouping, but you still want to force a page break. For example, you may want to split a report title across several pages. The solution is to use the Page Break tool from the Controls group on the ribbon. Just drag the control and drop it on the report where you want a page break to occur every time the page prints.
Be careful not to split the data in a control. Place page breaks above or below controls; do not overlap them.
Making the Report Presentation Quality
As you near completion of testing your report design, you should also test the printing of your report. Figure 9-33 shows the first page of the Product Display report. There are a number of things still to do to complete the report.
The report is very boring, plain, and not something you want to give to anyone else. If your goal is to just look at the data, this report is done. However, you need to do more before you are really done.
Although the report has good, well organized data, it is not of professional quality. To make a report more visually appealing, you generally add some lines and rectangles, possibly some special effects such as shadows or sunken areas if you have a background on the report. You want to make sure sections have distinct areas separate from each other using lines or color. Make sure controls aren’t touching each other (because text may eventually touch if a value is long enough). Make sure text is aligned with other text above or below and to the right or left.
In Figure 9-33, you can see some opportunities for professionalism.
Figure 9-33
The report is pretty plain and uninteresting at this point.
Adjusting the Page Header
The page header contains several large labels positioned far apart from each other. The column headers are too small and are just hanging there. They could be made one font size larger. The entire page header should be separated from the Detail section by a horizontal line.
If you wanted to add some color to your report, you could make the report name a different color. Be careful not to use too many colors unless you have a specific theme in mind. Most serious business reports use one or two colors, and rarely more than three with the exception of graphs and charts. Furthermore, colors are not much use when printed on most laser printers. Color laser printers are still not widely available, so adding a lot of color to your Access reports may not be something others will recognize or appreciate.
Figure 9-34 shows these changes. The Product Display label has been changed to a blue background color with white foreground text. This is done by first selecting the control and then selecting Blue for the background. They have also been placed under each other and left aligned. The rectangle around each of the controls was also properly sized by double-clicking each control’s sizing handles.
The next step is to add a nice thick line separating the Page Header section from the Category Group Header section:
1. Select the Line tool in the Controls ribbon group.
2. Place the mouse cursor near the far left side of the Page Header, just to the right and above of the 1 inch mark on the vertical toolbar, as shown in Figure 9-34.
Figure 9-34
Adjusting controls in the page header
3. Hold down the Shift key and then hold down the left mouse button and drag the mouse across the page header, releasing it just to the left of the 7 1/2-inch mark.
Holding down the Shift key forces a nice, straight, horizontal line.
4. Select the line and select the 2 pt line thickness from the Line Thickness icon on the toolbar, or select the 2 pt Border Width property from the line’s Property window.
The Line Thickness icon should be next to the Border icon on the formatting toolbar.
Creating an expression in the group header
Figure 9-34 also shows that the Category field has been replaced by an expression. If you place the value of the category in the Group Header section, it looks out of place and may not be readily identifiable. Most data values should have some type of label to identify what they are.
The expression =”Category: “ & [Category] displays Category: followed by a space and the value of the Category field (such as Category: Cars) in the text box. The & symbol (the concatenation operator) joins strings. Make sure you leave a space after the colon or the value will not be separated from the label. The text control has been bolded, underlined, and the font point size increased as well.
You may find that Access complains about a circular reference on the Category text box after you change the control’s ControlSource. This happens because the name of the control is Category, and the text box is bound to a field named Category. Access doesn’t understand that [Category] in the expression you entered as the ControlSource actually refers to the field, not the text box. (A text box’s value cannot be based on the text box’s contents—that’s the definition of circular reference.)
The solution is to rename the text box to distinguish it from its bound field. The logical name for this text box is txtCategory.
When you create a bound control, it uses the name of the data field as the default control name. Using the control in an expression without changing the name of your control, causes circular references. You must rename the control to something other than the original field name. This is another reason why a simple naming convention, such as prefixing text boxes with txt is such a good idea. You’ll avoid a lot of nagging problems by adopting a naming convention for the controls on your Access reports.
Follow these steps to complete the expression and rename the control:
1. Select the Category control in the Category Group Header section.
2. Display the Property window for the control.
3. Change the Control Source property to =“Category: ” & [Category].
4. Change the Name property to txtCategoryDisplay.
Changing the picture properties and the Detail section
The Detail section is in fairly good shape. Make sure the Description control is slightly indented from the Category expression in the Group Header. A label should be created, as shown in Figure 9-34, identifying the values in the Cost, Retail Price, and Sale Price controls.
A line is also good to add to this Detail section to separate one record from another. This is often done when records occupy varying space within a group. Some records are shorter than others, and the separation between records may not be obvious to users.
Because you don’t want two lines at the bottom of each page (you add a line to the Page Footer next), you put this line at the top of the Detail section:
1. Select the Line tool in the Controls ribbon group.
2. Place the cursor near the far left side of the Detail section, just to the right and above the 1/8-inch mark on the vertical toolbar, as shown in Figure 9-35.
Figure 9-35
Put the cursor near the far left of the Detail section.
You may have to reposition controls in the Detail section to make room for the horizontal line.
3. Hold down the Shift key and drag the line across the page header, releasing the mouse button just to the left of the 7 1/2-inch mark.
4. Select the line and select 1 or 2 pt line thickness from the Line Thickness icon in the Controls ribbon group, or select the 1 or 2 pt BorderWidth property from the line’s Property Sheet.
Numeric data controls are right aligned by default. Because they are next to each other horizontally and not above each other vertically, they can be left or center aligned. Although the repeating groups of records are above each other, they are separated by a wide space and left alignment is okay.
One task to complete is to change the picture control to make the picture fit within the control and to add a shadow to dress up the picture and give it some depth. Follow these steps to complete these tasks:
1. Select the Picture control in the Detail section.
2. Change the control’s Size Mode property to Stretch.
3. Select Shadowed as the Special Effect property.
Creating a standard page footer
The page footer currently contains a page number control that you added earlier in this chapter. Although Page n of m is at the bottom of the report, a date and time control would be nice as well. Many times, you print a copy of a report and then discover some bad data. You correct the values, print another copy, and discover you can’t tell them apart. Having a print date and time solves this problem.
Follow these steps to create a date/time control:
1. Select the Text Box control in the Controls group.
2. Select the Page Footer section and add a text box near the left edge.
3. Delete the label attached to the text box.
4. Enter =Now() as the text box’s Control Source property.
This displays the current date and time when the report is run. If you use the Date() keyword, you only get the current date and not the current time.
5. Select General Date from the control’s Format property.
6. Select Align Left from the Font ribbon group.
The print date control should be left aligned, but make sure the page number control is right-aligned.
The last step is to move the controls down a little from the Page Footer section and add a line between the Page Header section and these controls:
1. Select the date and page number controls and move them down 1/8 inch.
2. While they are selected, click the Italic icon on the Formatting toolbar.
3. Select the Line tool in the toolbox.
4. Draw a horizontal line above the print date and page number controls in the Page Footer section, and adjust its width.
Your screen should look like the one shown in Figure 9-36.
Figure 9-36
Adjusting controls in the Detail and Page Footer sections
If every even-numbered page is blank, you accidentally widened the report past the 8-inch mark. If you move a control to brush up against the right page-margin border or exceed it, the right page margin increases automatically. When it is past the 8-inch mark, it can’t display the entire page on one piece of paper. The blank page you get is actually the right side of the preceding page. To correct this, make sure that all your controls are within the 8-inch right margin; then drag the right page margin back to 8 inches.
Saving your report
After all the time you spent creating your report, you’ll want to save it. It is good practice to save your reports frequently, starting as soon as you create them. This prevents the frustration that occurs when you lose work because of a power failure. Save the report as follows:
1. Select File⇒Save, or click the Save button in the Quick Access toolbar in the upper -left area of the main Access window.
If this is the first time you have saved the report, the Save As dialog box appears.
2. Type a valid Access object name. For this example, type rptProductDisplayFinal.
3. Click OK.
If you already saved your report, Access immediately saves your report.
Summary
Reports are an important and integral part of most Access applications. Very often reports are the most important aspect of Access applications, and are seen by people who never see the Access application running on a computer.
Access is endowed with an outstanding Report Designer. This long chapter has surveyed the wealth of report creation tools available to the Access developer. As long as this chapter is, it has only scratched the surface and presented the fundamental capabilities of the Access Report Designer.
In this chapter, you read about the different types of Access reports, learned how to use the Access Report Wizard to build reports, and then created reports from scratch. You also read about the many different ways to provide a report with data and to display data on the report. This chapter also discussed a number of techniques for summarizing data on Access reports.