A Detail report supplies an entry for each record included in the report. As you can see in Figure 6.1, there’s an entry for each order in the Orders table during the specified period (1/1/1995-12/31/1996). The report’s detail is grouped by country and within country by salesperson and gives you subtotals by salesperson and country. The bottom of the report has grand totals for all records included in the report. The report is based on a Parameter query that limits the data displayed on the report based on criteria supplied by the user at runtime.

Figure 6.1. An example of a Detail report.

A Summary report gives you summary data for all the records included in the report. In Figure 6.2, only total sales by quarter and year are displayed in the report. The underlying detail records that compose the summary data aren’t displayed in the report. The report is based on a query that summarizes the net sales by OrderID. The report itself contains no controls in its Detail section. All controls are placed in report Group Headers and Footers that are grouped on the quarter and year of the ship date. Because no controls are found in the report’s Detail section, Access prints summary information only.

Figure 6.2. An example of a Summary report.

Cross-tabulation reports display summarized data grouped by one set of information on the left side of the report and another set across the top. The report shown in Figure 6.3 shows total sales by product name and employee. The report is based on a Crosstab query and is generated using a fair amount of VBA code. This code is required because each time the report is run, a different number of employees might need to be displayed in the report’s columns. In other words, the number of columns needed might be different each time the report is run. This report and the techniques needed to produce it are covered in Chapter 10, “Advanced Report Techniques.”

Figure 6.3. An example of a Cross-tabulation report.

Although the statement “A picture paints a thousand words” is a cliché, it’s also quite true—research proves that you retain data much better when it’s displayed as pictures rather than numbers. Fortunately, Access makes including graphics and charts in your reports quite easy. As shown in Figure 6.4, a report can be designed to combine both numbers and charts. The report in Figure 6.4 shows the sales by product, both as numbers and as a bar chart. The main report is grouped by product category and contains a subreport based on a query that summarizes sales by CategoryID, CategoryName, and ProductName for a specific date range. The chart totals product sales by product name, displaying the information graphically.

Figure 6.4. An example of a report with a chart.

Users often need a report that looks like a printed form. The Access Report Builder, with its many graphical tools, allows you to quickly produce reports that emulate the most elegant data-entry form. The report shown in Figure 6.5 produces an invoice for a customer. The report is based on a query that draws information from the Customers, Orders, Order Details, Products, Employees, and Shippers tables. The report’s Filter property is filled in, limiting the data that appears on the report to the last six records in the Orders table. Using graphics, color, fonts, shading, and other special effects gives the form a professional look.

Figure 6.5. An example of a report containing a form.

Creating mailing labels in Access 2002 is easy using the Label Wizard. Mailing labels are simply a special type of report with a page setup indicating the number of labels across the page and the size of each label. An example of a mailing label report created by using the Label Wizard is shown in Figure 6.6. This report is based on the Customers table but could have just as easily been based on a query that limits the mailing labels produced.

Figure 6.6. An example of a report containing mailing labels.

To create a report with the Report Wizard, click Reports in the Objects list and then double-click the Create Report by Using Wizard icon. This launches the Report Wizard. The first step is to select the table or query that will supply data to the report. I prefer to base my reports on queries, or on embedded SQL statements (a query stored as part of a report). This generally improves performance because it returns as small a dataset as possible. In a client/server environment, this is particularly pronounced because the query is usually run on the server and only the results are sent over the network wire. Basing reports on queries also enhances your ability to produce reports based on varying criteria.

After you have selected a table or query, you can select the fields you want to include on the report. The fields included in the selected table or query are displayed in the list box on the left. To add fields to the report, double-click the name of the field you want to add or click the field name and click the > button. In the example in Figure 6.9, five fields have been selected from the tblClients table.

Figure 6.9. The first step of the Report Wizard: table/field selection.

After you have selected a table or query and the fields you want to include on the report, click Next. You’re then prompted to add group levels, which add report groupings, to the report. Add group levels if you need to visually separate groups of data or include summary calculations (subtotals) in your report. Report groupings are covered later in this chapter. If your report doesn’t require groupings, click Next.

In the third step of the Report Wizard, you choose sorting levels for your report. Because the order of a query underlying a report is overridden by any sort order designated in the report, it’s a good idea to designate a sort order for the report. You can add up to four sorting levels with the wizard. In the example shown in Figure 6.10, the report is sorted by the ClientID field. After you select the fields you want to sort on, click Next.

Figure 6.10. The third step of the Report Wizard: sorting report data.

In the fourth step of the Report Wizard, you decide on the report’s layout and orientation. The layout options vary depending on what selections have been made in the wizard’s previous steps. The orientation can be Portrait or Landscape. This step of the Report Wizard also allows you to specify whether you want Access to adjust the width of each field so that all the fields fit on each page. After supplying Access with this information, click Next.

You choose a style for your report in the Report Wizard’s fifth step. The choices are Bold, Casual, Compact, Corporate, Formal, and Soft Gray. You can preview each look before you make a decision. Any of the style attributes applied by the Report Wizard, as well as other report attributes defined by the wizard, can be modified in Report Design view any time after the wizard has produced the report. After you have selected a style, click Next.

The final step of the Report Wizard prompts you for the report’s title. This title is used as both the name and the caption for the report. I supply a standard Access report name and modify the caption after the Report Wizard has finished its process. You’re then given the opportunity to preview the report or modify the report’s design. If you opt to modify the report’s design, you’re placed in Design view. (See Figure 6.11.) The report can then be previewed at any time. You can optionally mark the check box Display Help on working with the Report to have Access display the help window and list the associated report topics.

Figure 6.11. Design view of a completed report.

Although you usually get started with most of your reports by using a Report Wizard, you should understand how to create a new report from Design view. To create a report without using a wizard, click Reports in the Objects list and then double-click the Create Report in Design View icon. The Report Design window appears. You must then set the Record Source of the report to the table or query upon which you want the report to be based. Another way to create a report from Design view is to click Reports in the Objects list and then click New to open the New Report dialog box. Click Design view and use the drop-down list to select the table or query on which the report will be based; then click OK. The Report Design window appears.

To help you design reports, several report design tools are available, including the Properties, Toolbox, Field List, and Sorting and Grouping windows. Two toolbars are also available to make developing and customizing your reports easier: the Report Design toolbar and the Formatting toolbar. The Report Design toolbar offers tools for saving, previewing, and printing your report and for cutting, copying, and pasting report objects. The Formatting toolbar is specifically designed to help you customize the look of your report. It includes tools for changing the font, font size, alignment, color, shading, and other physical attributes of the report objects.

The Properties, Toolbox, Field List, and Sorting and Grouping windows are all designed as toggles. This means that buttons on the Report Design toolbar alternately hide and show these valuable windows. If you have a high-resolution monitor, you might want to leave the windows open at all times. If you have a low-resolution monitor, you need to get a feel for when it’s most effective for each window to be opened or closed.

Fields can most easily be added to a report by using the Field List window. With the Field List window open, click and drag a field from the field list onto the appropriate section of the report. Several fields can be added at one time, just as they can in forms. Use the Ctrl key to select noncontiguous fields, use the Shift key to select contiguous fields, or double-click the field list’s title bar to select all the fields; then click and drag them to the report as a unit.

Microsoft Access offers several techniques to help you select, move, align, and size report objects. Different techniques are effective in different situations. Experience will tell you which technique you should use and when. Selecting, moving, aligning, and sizing report objects are quite similar to performing the same tasks with form objects. The techniques are covered briefly in this chapter; for a more detailed explanation of each technique, refer to Chapter 5, “What Every Developer Needs to Know About Forms.”

Selecting Report Objects

To select a single report object, click it; selection handles appear around the selected object. After the object is selected, you can modify any of its attributes (properties), or you can size, move, or align it.

To select multiple objects so you can manipulate them as a unit, use one of the following techniques:

1. Hold down the Shift key as you click multiple objects. Each object you click is then added to the selection.

2. Place your mouse pointer in a blank area of the report. Click and drag to lasso the objects you want to select. When you let go of the mouse, any object even partially within the lasso is selected.

3. Click and drag within the horizontal or vertical ruler. As you click and drag, lines appear indicating the potential selection area. When you release the mouse, all objects within the lines are selected.

Note

Make sure that you understand which objects are actually selected; attached labels can cause some confusion. Figure 6.12 shows a report with four objects selected: the rptClients label, the Contact First Name label, the City text box, and the ContactFirstName text box. The City label is not selected. It has one selection handle because it’s attached to the City text box. If you were to modify the properties of the selected objects, the City label would be unaffected.

Figure 6.12. Selecting objects in an Access report.

Labels are used to display information to your users. They’re commonly used as report headings, column headings, or group headings for your report. Although the text they display can be modified at runtime by using VBA code, they can’t be directly bound to data.

To add a label to a report, select the Label tool in the toolbox; then click and drag to place the label on the report.

Text boxes are used to display field information or the result of an expression. They are used throughout a report’s different sections. For example, in a Page Header, a text box might contain an expression showing the date range that’s the criteria for the report. In a Group Header, a text box might be used to display a heading for the group. The possibilities are endless because a text box can hold any valid expression.

To add a text box to a report, select the Text Box tool from the toolbox. Click and drag the text box to place it on the report. A text box can also be added to a report by dragging a field from the field list to a report. This works as long as the field’s Display control property is a text box.

Lines can be used to visually separate objects on your report. For example, a line can be placed at the bottom of a section or underneath a subtotal. To add a line to a report, click the Line tool to select it; then click and drag to place the line on your report. When added, the line has several properties that can be modified to customize its look.

Rectangles can be used to visually group items that logically belong together on the report. They can also be used to make certain controls on your report stand out. I often draw rectangles around important subtotal or grand total information that I want to make sure that the report’s reader notices.

To add a rectangle to a report, select the Rectangle tool from the toolbox; then click and drag to place the rectangle on the report.

Bound object frames let you display the data in OLE fields, which contain objects from other applications, such as pictures, spreadsheets, and word processing documents.

To add a bound object frame to a report, click the Bound Object Frame tool in the toolbox; then click and drag the frame onto the report. Set the Control Source property of the frame to the appropriate field. You can also add a bound object frame to a report by dragging and dropping an OLE field from the field list onto the report.

Unbound object frames can be used to add logos and other pictures to a report. Unlike bound object frames, however, they aren’t tied to underlying data.

To add an unbound object frame to a report, click the Unbound Object Frame tool in the toolbox. Click and drag the object frame to place it on the report. This opens the Insert Object dialog box, shown in Figure 6.13, which you use to create a new OLE object or insert an existing OLE object from a file on disk. If you click Create From File, the Insert Object dialog box changes to look like Figure 6.14. Click Browse and locate the file you want to include in the report. The Insert Object dialog box gives you the option of linking to or embedding an OLE object. If you select Link, a reference is created to the OLE object. Only the bitmap of the object is stored in the report, and the report continues to refer to the original file on disk. If you don’t select Link, the object you select is copied and embedded in the report and becomes part of the Access MDB file; no link to the original object is maintained.

Figure 6.13. Use the Insert Object dialog box to insert a new or existing object into an unbound object frame.

Figure 6.14. The Insert Object dialog box with Create from File selected.

Note

It’s usually preferable to use an image control rather than an unbound object frame for static information like a logo because the image control requires much fewer resources than does an unbound object frame. Image controls are covered in the next section; Figure 6.15 shows a report with an image control.

Figure 6.15. A report with an image control.

Image controls are your best option for displaying static images, such as logos, on a report. An unbound object can be modified after it is placed on a report, but you can’t open the object application and modify an image when it’s placed on a report. This limitation, however, means far fewer resources are needed, so performance improves noticeably.

As mentioned earlier in this section, it’s standard to include mostly labels and text boxes on your reports, but other controls can be added when appropriate. To add any other type of control, click to select the control; then click and drag to place it on the report.

When the report is selected, the Properties window shows all the properties associated with the report. To select the report and open the Properties window at the same time, double-click the Report Selector. A report has 47 properties available on the property sheet (there are additional properties available only from code) broken down into the appropriate categories in the Properties window. Forty of the properties relate to the report’s format, data, and other special properties; the remaining seven relate to the events that occur when a report is run. The format, data, and other properties are covered here, and the event properties are covered in Chapter 10.

A report has the following 23 Format properties for changing the report’s physical appearance:

Caption: The Caption property of the report is the text that appears in the Report window’s title bar when the user is previewing the report. It can be modified at runtime to customize it for a particular situation.

Auto Resize: The Auto Resize property is new in Access 2002. This setting determines whether a report is resized automatically to display all the data on the report.

Auto Center: The Auto Center property is also new in Access 2002. You use the Auto Center property to designate whether you want the Report window to automatically be centered on the screen. Auto Center property is also new in Access 2002. This property specifies whether a report is centered automatically within the application window whenever it is opened.

Page Header, Page Footer: The Page Header and Page Footer properties determine on what pages these sections appear. The options are All Pages, Not with Rpt Hdr, Not with Rpt Ftr, and Not with Rpt Hdr/Ftr. Because you might not want the Page Header or Page Footer to print on the Report Header or Report Footer pages, these properties give you control over where those sections print.

Grp Keep Together: In Access, you can keep a group of data together on the same page by using the Grp Keep Together property. The Per Page option forces the group of data to remain on the same page, and the Per Column option forces the group of data to remain within a column. A group of data refers to all the data within a report grouping (for example, all the customers in a city).

Border Style: The Border Style property is a new property for Access 2002 reports. Like its form counterpart, it is far more powerful than its name implies. The options for the Border Style property are None, Thin, Sizable, and Dialog. A border style set to None means the report has no border. A Thin border is not resizable; the Size command isn’t available in the Control menu. This setting is a good choice for pop-up reports, which remain on top even when other forms or reports are given the focus. A Sizable border is standard for most reports. It includes all the standard options in the Control menu. A Dialog border looks like a Thin border. A report with a border style of Dialog can’t be maximized, minimized, or resized. Once the border style of a report is set to Dialog, the Maximize, Minimize, and Resize options aren’t available in the report’s Control menu. Border Style property is new in Access 2002. This setting lets you specify the type of border to be used for the Report window under print preview mode. It also determines whether the Report window is sizable and which border elements are available for the Report window such as the title bar, Close button, the Control menu, and the Minimize and Maximize buttons.

Control Box: The Control Box property is new in Access 2002 as well. This property lets you specify the whether the Report window under print preview has the Control menu available. The Control menu is activated by clicking the icon in the upper-left corner of a window and displays options for manipulating the window: Restore, Move, Size, Minimize, Maximize, and Close.

Min Max Buttons: The Min Max Buttons property is also new in Access 2002. This property lets you specify whether the Minimize and/or Maximize options should be available from the Control menu for the Report window in Print Preview mode. You can select from None, Min Enabled, Max Enabled, or Both Enabled.

Close Button: The Close Button property is new in Access 2002. This setting specifies whether to enable or disable the close button on the Print Preview window.

Width: The Width property specifies the width of the report sections.

Picture, Picture Type, Picture Size Mode, Picture Alignment, Picture Tiling, and Picture Pages: The background of a report can be a picture. The Picture properties determine what picture is used as a background for the report and what attributes are applied to it.

Grid X/Grid Y: The Grid X and Grid Y properties determine the density of the gridlines in the Report Design window.

Layout for Print: The Layout for Print property specifies whether screen or printer fonts are used in the report. If you want to optimize reports for preview, select No; if you want to optimize reports for the printer, select Yes. This option is not as important if you select TrueType fonts because TrueType fonts usually print equally well to the screen and printer.

Palette Source: The Palette Source property determines the source for the report’s selectable color.

Orientation: The Orientation property is used for taking advantage of language-specific versions of Microsoft Access, such as Arabic. This property can be set to support right-to-left display features for these language-specific editions of Access, provided that the underlying operating system supports that language and is 32-bit (for example, Windows 2000).

Moveable: The Moveable property determines whether the user can move the Report window around the screen by clicking and dragging the report by its title bar.

A report has the following five Data properties used to supply information about the data underlying the report:

Record Source: The Record Source property specifies the table or query whose data underlies the report. The record source of a report can be modified at runtime. This aspect of the Record Source property makes it easy for you to create generic reports that use different record sources in different situations.

Filter: The Filter property allows you to open the report with a specific filter set. I usually prefer to base a report on a query rather than apply a filter to it. At other times, it’s more appropriate to base the report on a query, but then apply and remove a filter as required, based on the report’s runtime conditions.

Filter On: The Filter On property determines whether a report filter is applied. If the value of this property is set to No, the Filter property of the report is ignored.

Order By: The Order By property determines how the records in a report are sorted when the report is opened.

Order By On: The Order By On property determines whether the Order By property of the report is used. If the value of this property is No, the report’s Order By property is ignored.

A report has 12 other properties; these miscellaneous properties, shown in the following, allow you to control other important aspects of the report:

Record Locks: The Record Locks property determines whether the tables used in producing the report are locked while the report is being run. The two values for this property are No Locks and All Records. No Locks is the default value; it means that no records in the tables underlying the report are locked while the report is being run. Users can modify the underlying data as the report is run, which can be disastrous when running sophisticated reports. The data in the report can be changed as the report is being run, which would make figures for totals and percent of totals invalid. Although the All Records option for this property locks all records in all tables included in the report (thereby preventing data entry while the report is being run), it might be a necessary evil for producing an accurate report.

Date Grouping: The Date Grouping property determines how grouping of dates occurs in your report. The US Defaults option means that Access uses United States’ defaults for report groupings; therefore, Sunday is the first day of the week, the first week begins January 1, and so on. The Use System Settings option means that date groupings are based on the locale set in the Control Panel’s Regional Settings, rather than on U.S. defaults.

Pop Up: The Pop Up property determines whether the report’s print preview window opens as a pop-up window. Within Microsoft Access, pop-up windows always remain on top of other open windows.

Modal: The Modal property instructs Access to open the Report window in a modal or modeless state. The default is No, meaning that the window will not be opened as modal. A modal window retains the application program’s focus until the window receives the appropriate user input that it requires.

Menu Bar: The Menu Bar property allows you to associate a custom menu bar with the report that’s visible when the user is previewing the report. Adding a custom menu to your report lets you control what the user can do while the report is active.

Toolbar: The Toolbar property lets you associate a custom toolbar with the report that’s visible when the user is previewing the report.

Shortcut Menu Bar: The Shortcut Menu Bar property determines what shortcut menu is associated with the report while the report is being previewed. The shortcut menu bar appears when the user clicks the right mouse button over the preview window.

Fast Laser Printing: The Fast Laser Printing property determines whether lines and rectangles are replaced with text character lines when you print a report with a laser printer. If fast printing is your objective and you’re using a laser printer, you should set this property to Yes.

Help File, Help Context ID: The Help File and Help Context ID properties let you associate a help file and help topic with the report.

Tag: The Tag property is an extra property for storing information defined by the user at either design time or runtime. It is Microsoft Access’s way of giving you an extra property. Access makes no use of this property; if you don’t take advantage of it, it will never be used.

Has Module: The Has Module property determines whether the report contains an associated class module. If no code will be included in the report, eliminating the class module can both improve performance and reduce the size of the application database. A report without a class module is considered a “lightweight object,” which loads and displays faster than an object with an associated class module.

You can modify several formatting properties of the selected objects by using the formatting toolbar. If you prefer, you can set all the properties in the Properties window:

Format: The Format property determines how the data in the control is displayed. This property is automatically inherited from the underlying field. If you want the control’s format on the report to differ from the underlying field’s format, you must set the Format property of the control.

Caption: The Caption property specifies the text displayed for labels and command buttons. A caption is a string containing up to 2,048 characters.

Hyperlink Address: The Hyperlink Address property is a string representing the path to a UNC (network path) or URL (Web page). Command buttons, image controls, and labels all contain the Hyperlink Address property.

Hyperlink SubAddress: The Hyperlink SubAddress property is a string representing a location within the document specified in the Hyperlink Address property. Command buttons, image controls, and labels all contain the Hyperlink SubAddress property.

Decimal Places: The Decimal Places property defines the number of decimal places displayed for numeric values.

Visible: The Visible property determines whether a control is visible. In many cases, you will want to toggle the visibility of a control in response to different situations.

Hide Duplicates: The Hide Duplicates property hides duplicate data values in a report’s Detail section. Duplicate data values occur when one or more consecutive records in a report contain the same value in one or more fields.

Can Grow, Can Shrink: The Can Grow property, when set to Yes, allows a control to expand vertically to accommodate all the data in it. The Can Shrink property eliminates blank lines when no data exists in a field for a particular record. For example, if you have a second address line on a mailing label, but there’s no data in the Address2 field, you don’t want a blank line to appear on the mailing label.

Left, Top, Width, Height: These properties set the size and position of the controls on a report.

Back Style, Back Color: The Back Style property can be set to Normal or Transparent. When set to Transparent, the color of the report shows through to the control. When set to Normal, the control’s Back Color property determines the object’s color.

Special Effect: The Special Effect property adds 3D effects to a control.

Border Style, Border Color, Border Width: These properties set the physical attributes of a control’s border.

Fore Color: This property sets the color of the text within the control.

Font Color, Font Name, Font Size, Font Weight, Font Italic, Font Underline: The border properties affect the control’s border, but the font properties affect the appearance of the text within the control.

Text Align: The Text Align property sets the alignment of the text within the control. It can be set to Left, Center, Right, or Distribute. When set to Distribute, text is justified.

Reading Order: The Reading Order property determines the visual order in which characters, words, and groups of words are displayed. This property is often used with language-specific editions of Microsoft Access where the reading order needs to be changed. The default setting is Context; Left-to-Right and Right-to-Left are the other available settings.

Scroll Bar Align: The Scroll Bar Align property specifies the visual placement of the control’s vertical scrollbars and buttons. This property also works in conjunction with language-specific versions of Access to determine scrollbar placement in either the right-to-left or left-to-right direction. The default setting is System, which lets the operating system determine the scrollbar alignment.

Numeral Shapes: The Numeral Shapes property determines the format for displaying numeric characters. This property also works in conjunction with language-specific versions of Access to determine the type of numeric character to display. The default setting is System, which lets the operating system determine the numeric character display format. The other settings include Arabic, National, and Context.

Left Margin, Top Margin, Right Margin, Bottom Margin: These properties are used to determine how far the text within the control prints from the left, top, right, and bottom of the control. These properties are particularly useful for large controls containing a lot of text, such as a memo on an invoice.

Line Spacing: The Line Spacing property is used to control the spacing between lines of text within a control. The Line Spacing property is designated in inches.

Is Hyperlink: The Is Hyperlink property is used to determine whether the text within the control is displayed as a hyperlink. If the Is Hyperlink property is set to Yes, and the text within the control is a relevant link, the text will serve as a hyperlink. (This is useful only if you save the report in HTML format.)

The Other properties of a control designate properties that don’t fit into any other category, such as:

Name: The Name property gives you an easy and self-documenting way to refer to the control in VBA code and in many other situations. You should name all your controls. Naming conventions for report controls are the same as those for form controls. Refer to Appendix B, “Naming Conventions,” for more detailed information.

Vertical: The Vertical property is used to determine whether the text within the control is displayed vertically. The default value for this property is No.

Tag: Like the Tag property of a report, the Tag property of a control gives you a user-defined slot for the control. You can place any extra information in the Tag property.

You can create one-to-many reports by using a Report Wizard, or you can build the report from scratch. Different situations require different techniques, some of which are covered in the following sections.

Building a One-to-Many Report with the Report Wizard

Building a one-to-many report with the Report Wizard is quite easy; just follow these steps:

1. Click Reports in the Objects list and double-click Create Report by Using Wizard.

2. Use the Tables/Queries drop-down list to select the first table or query whose data will appear on the report.

3. Select the fields you want to include from that table.

4. Select each additional table or query you want to include on the report, selecting the fields you need from each.

5. Click Next. Step 2 of the Report Wizard offers a suggested layout for your data. (See Figure 6.16.) You can accept Access’s suggestion, or you can choose from any of the available layout options. Click Next.

   

   Figure 6.16. Step 2 of the Report Wizard: selecting a layout.

6. Step 3 of the Report Wizard asks whether you want to add any grouping levels. Grouping levels can be used to visually separate data and to provide subtotals. In the example in Figure 6.17, the report is grouped by city and company name. After you select grouping levels, click Next.

   

   Figure 6.17. Step 3 of the Report Wizard: selecting groupings.

7. Step 4 of the Report Wizard lets you select how you want the records in the report’s Detail section to be sorted. (See Figure 6.18.) This step of the wizard also allows you to specify any summary calculations you want to perform on the data. (See Figure 6.19.) Click the Summary Options to specify the summary calculations. Using the button you can even opt to include the percent of total calculations.

   

   Figure 6.18. Step 4 of the Report Wizard: selecting a sort order.

   

   Figure 6.19. Adding summary calculations.

8. In step 5 of the Report Wizard, you select the layout and orientation of your report. Layout options include Stepped, Blocked, Outline 1, Outline 2, Align Left 1, and Align Left 2.

9. Step 6 of the Report Wizard lets you select from predefined styles for your report, including Bold, Casual, Compact, Corporate, Formal, and Soft Gray. You can preview each style to see what it looks like.

10. In step 7 of the Report Wizard, you select a title for your report. The title also becomes the name for the report. I like to select an appropriate name and change the title after the wizard is finished. The final step also allows you to determine whether you want to immediately preview the report or to see the report’s design first.

The report created in the previous example is shown in Figure 6.20. Notice that the report is sorted and grouped by City and CompanyName. The report’s data is in order by OrderDate and ProductName within a CompanyName grouping.

Figure 6.20. A completed one-to-many report.

This method of creating a one-to-many report is by far the easiest. In fact, the “background join” technology that the wizards use when they allow you to pick fields from multiple tables—figuring out how to build the complex queries needed for the report or form—was one of the major enhancements in Access 95. It’s a huge timesaver and helps hide unnecessary complexity from you as you build a report. Although you should take advantage of this feature, it’s important that, as a developer, you know what’s happening under the covers. The following two sections give you this necessary knowledge.

Building a Report Based on a One-To-Many Query

Another popular method of building a one-to-many report is from a one-to-many query. A one-to-many report built in this way is constructed as though it were based on the data within a single table. First, you build the query that will underlie the report. (See Figure 6.21.)

Figure 6.21. An example of a query underlying a one-to-many report.

When you have finished the query, you can select it rather than select each individual table (as done in the previous section). After the query is selected, creating the report is the same process as the one used for the previous report.

Building a One-to-Many Report with the Subreport Wizard

A one-to-many report can also be created by building the parent report and then adding a Subform/Subreport control. This is often the method used to create reports such as invoices that show the report’s data in a one-to-many relationship rather than in a denormalized format (as shown in Figure 6.20). If you want to use the Subform/Subreport Wizard, you must make sure that the Control Wizards tool is selected before you add the Subform/Subreport control to the main report. Here is the process:

1. Click to select the Subform/Subreport control tool.

2. Click and drag to place the Subform/Subreport control on the main report. The Subform/Subreport control is usually placed in the report’s Detail section. When you have placed the Subform/Subreport control on the report, the SubReport Wizard is invoked.

3. Indicate whether you want the subreport to be based on an existing report or form or you want to build a new subreport based on a query or table. Click Next.

4. If you select Table or Query, you have to select the table or query on which the subreport will be based. You can then select the fields you want to include on the subreport. You can even select fields from more than one table or query. When you’re finished, click Next.

5. The next step of the SubReport Wizard suggests a relationship between the main report and the subreport. (See Figure 6.22.) You can accept the selected relationship, or you can define your own. When you’re finished, click Next.

   

   Figure 6.22. The Subreport Wizard: identifying the relationship.

6. The final step of the SubReport Wizard asks you to name the subreport. To follow standards, the name should begin with the prefix rsub. Click Finish when you’re finished.

As you can see in Figure 6.23, the one-to-many relationship between two tables is clearly highlighted by this type of report. In the example, each customer is listed. All the detail records reflecting the orders for each customer are listed immediately following each customer’s data.

Figure 6.23. A completed one-to-many report created with the Subreport Wizard.

When a subreport has been added to a report, it’s important to understand what properties have been set by the SubReport Wizard so that you can modify the Subreport control, if needed. You should become familiar with a few properties of a subreport:

Source Object: The name of the report or other object that’s being displayed within the control.

Link Child Fields: The fields from the child report that link the child report to the master report.

Link Master Fields: The fields from the master report that link the master report to the child report.

Can Grow: Determines whether the control can expand vertically to accommodate data in the subreport.

Can Shrink: Determines whether the control can shrink to eliminate blank lines when no data is found in the subreport.

Not only should you know how to work with the properties of a Subreport object, but you should also be able to easily modify the subreport from within the main report. You can always modify the subreport by selecting it within the list of reports in the Database window. To do this, click the report you want to modify; then click Design. You can also modify a subreport by selecting its objects directly within the parent report.

Often, you want to add sorting or grouping to a report. To do so, follow these four steps:

Each grouping in a report has properties that define the group’s attributes. Each group has five properties that determine whether the field or expression is used for sorting, grouping, or both. (See Figure 6.26.) They are also used to specify details about the grouping options. Here are the Sorting and Grouping properties:

Group Header: The Group Header property specifies whether the selected group contains a header band. When you set the Group Header property to Yes, an additional band appears in the report that can be used to display information about the group. For example, if you’re grouping by country, the Group Header is used to display the name of the country you’re about to print. If the Group Header and Group Footer properties are both set to No, the field is used only to determine the sort order of the records in the report.

Group Footer: The Group Footer property specifies whether the selected group contains a footer band. When you set the Group Footer property to Yes, an additional band appears in the report. The Group Footer band can be used to display summary information about the group; it’s often used to display subtotals for the group.

Group On: The Group On property specifies what constitutes a new group. It’s often used for situations such as departmental roll-ups. Rather than grouping on the entire department number, you might want to group on the first three digits, for example.

The Group On choices for text fields are Each Value and Prefix Characters. For Date fields, the choices are much more complex. They include Each Value, Year, Qtr, Month, Week, Day, Hour, and Minute. This means you could group by a Date field and have Access subtotal and begin a new group each time the week changes in the field. For AutoNumber, Currency, and Number fields, the choices are Each Value and Interval.

Group Interval: The Group Interval property is used with the Group On property to specify an interval value by which data is grouped. If, for example, the Group On property for a text field is set to Prefix Characters, and the Group Interval is set to 3, the field’s data is grouped on the first three characters.

Keep Together: The Keep Together property determines whether Access tries to keep an entire group together on one page. The three choices for the property are No, Whole Group, and With First Detail. The Whole Group option means that Access tries to keep the entire group together on one page. This includes the Group Header, Group Footer, and Detail section. The With First Detail option means that Access prints the group header on a page only if it can also print the first detail record on the same page.

Figure 6.26. The Sorting and Grouping window, showing the five sorting and grouping properties.

Each Group Header and Footer has its own properties that determine the behavior of the Group Header or Footer:

Force New Page: The Force New Page property can be set to None, Before Section, After Section, or Before & After. When set to None, no page break occurs either before or after the report section. If set to Before Section, a page break occurs before the report section prints; if set to After Section, a page break occurs after the report section prints. When set to Before and After, a page break occurs before the report section prints as well as after it prints.

New Row or Col: The New Row or Col property determines whether a column break occurs whenever the report section prints. This property applies only to multicolumn reports. The choices are None, Before Section, After Section, and Before & After. Like the Force New Page property, this property determines whether the column break occurs before the report section prints, after it prints, or before and after, or whether it’s affected by the report section break at all.

Keep Together: The Keep Together property specifies whether you want Access to try to keep an entire report section together on one page. If this property is set to Yes, Access starts printing the section at the top of the next page if it can’t print the entire section on the current page. When set to No, Access prints as much of the section as possible on the current page, inserting each page break as necessary. If a section exceeds the page length, Access starts printing the section on a new page and continues printing it on the following page.

Visible: The Visible property indicates whether the section is visible. It’s common to hide the visibility of a particular report section at runtime in response to different situations. This can easily be done by changing the value of the report section’s Visible property with VBA code, usually on the Format event.

Can Grow, Can Shrink: The Can Grow property determines whether the section stretches vertically to accommodate the data in it. The Can Shrink property specifies whether you want the section to shrink vertically, eliminating blank lines.

Repeat Section: The Repeat Section property is a valuable property; it lets you specify whether the group header is repeated on subsequent pages if a report section needs more than one page to print.

Hyperlinks can be added to reports in the form of labels. When added, they serve as a direct link to a UNC or URL. To add a hyperlink to a report, follow these steps:

The Hyperlink Address and Hyperlink SubAddress properties come into play only when a report is saved as HTML and viewed in a Web browser, such as Internet Explorer 5.0. Saving a report as an HTML document is covered in the following section.

To save a report as HTML, choose File|Export. Use the Save As Type drop-down list to select HTML documents (*.htm, *.html). Pick a location and name for the file, and then click Export. The document is saved as HTML and assigned the name and location you specified. Click Autostart to display the report in the default Web browser after the HTML is generated.

Reports can also be saved as XML by selecting the report within the database window (or by having the report open and in focus) and clicking File|Export from the menu bar. Choose XML Documents (*.xml) from the Save As Type drop-down. Accept the default filename or specify a different filename and click Export. Access generates three files: filename.htm, filename.xsl, and filename.xml. The .xsl file is the stylesheet for displaying the XML data. When you open the .htm file within a browser, the report is displayed along with the data. If you open the .xml file within a browser, you are viewing the actual XML code. Saving reports as XML is covered in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.

The rptClientListing report lists all the clients in the tblClients table. The report includes the company name, contact name, intro date, default rate, and term type of each customer. The report is grouped by contact type and sorted by company name. It provides the average default rate by contact type and overall.

The rptClientListing report is based on a query called qryClientListing, which is shown in Figure 6.27. The query includes the CompanyName, IntroDate, and DefaultRatefields from the tblClients table. It joins the tblClients table to the tblContactType table to obtain the ContactType field from tblContactType, and joins the tblClients table to the tblTerms table to obtain the TermType field from the tblTerms table. It also includes an expression called ContactName that concatenates the ContactFirstName and ContactLastName fields. The expression looks like this:

ContactName: [ContactFirstName] & " " & [ContactLastName]

Figure 6.27. The qryClientListing query—a foundation for the rptClientListing report.

To build the report, follow these steps:

The rptTimeSheet report is much more complex than the rptClientListing report. It includes two subreports: rsubTimeSheet and rsubTimeSheetExpenses.

The rptTimeSheet report is shown in Figure 6.32. It’s based on a query called qryTimeSheet. (See Figure 6.33.) It contains fields from both tblTimeCards and tblEmployees.

Figure 6.32. The rptTimeSheet report in Design view.

Figure 6.33. The qryTimeSheet query in Design view.

The rptTimeSheet report has a Page Header that includes the title of the report, but nothing else is found within the Page Header. The TimeCardID header contains the EmployeeName and DateEntered from the qryTimeSheet query. The report’s Detail section contains the two subreports rsubTimeSheet and rsubTimeSheetExpenses. The TimeCardID footer has a text box that contains the grand total of hours and expenses. The expression within the text box is

=[rsubTimeSheet].[Report]![txtTotalHourlyBillings]+[rsubTimeSheetExpenses]
.[Report]![txtTotalExpenseAmount]

It is easiest to build the expression using the expression builder.

The Page Footer holds two expressions, one for the date and another for the page number. They look like this:

=Now()
="Page " & [Page] & " of " & [Pages]

The rsubTimeSheet report is based on qrySubTimeSheet; this query contains the following fields from the tblProjects and tblTimeCardHours tables:

The design of rsubTimeSheet is shown in Figure 6.34. This subreport can easily be built from a wizard. Select all fields except TimeCardID and TimeCardDetailID from qrySubTimeSheets. View the data by tblTimeCardHours. Don’t add any groupings, and don’t sort the report. When you’re finished with the wizard, modify the design of the report. Remove the caption from the Report Header, and move everything from the Page Header to the Report Header. Collapse the Page Header, remove everything from the Page Footer, and add a Report Footer with the expression =Sum([HourlyBillings]).

Figure 6.34. The rsubTimeSheet report in Design view.

Change the format of the HourlyBillings and the TotalHourlyBillings controls to Currency. Use the Sorting and Grouping window to sort by TimeCardID and TimeCardDetailID.

The rsubTimeSheetExpenses report is based on qrySubTimeSheetExpense, which contains the following fields from the tblProjects, tblExpenseCodes, and tblTimeCardExpenses tables:

The design of rsubTimeSheetExpenses is shown in Figure 6.35. This subreport can easily be built from a wizard. Select all fields except TimeCardID and TimeCardExpenseID from qrySubTimeSheetExpense. View the data by tblTimeCardExpenses. Don’t add any groupings, and don’t sort the report. When you’re finished with the wizard, modify the design of the report. Remove the caption from the Report Header, and move everything from the Page Header to the Report Header. Collapse the Page Header, remove everything from the Page Footer, and add a Report Footer with the expression =Sum(ExpenseAmount).

Figure 6.35. The rsubTimeSheetExpenses report in Design view.

Change the format of the ExpenseAmount and the TotalExpenseAmount controls to Currency, and use the Sorting and Grouping window to sort by TimeCardID and TimeCardExpenseID.