Objective group 5. Modify reports in Layout view – MOS Study Guide for Microsoft Access Expert Exam MO-500

Objective group 5

Modify reports in Layout view

The skills tested in this section of the Microsoft Office Specialist Expert exam for Microsoft Access 365 and Microsoft Access 2019 relate to modifying and formatting reports. Specifically, the following objectives are associated with this set of skills:

5.1 Configure report controls

5.2 Format reports

You can use reports to create a filtered view of your data, to group and summarize data, and to provide data in a format that’s suitable for sharing, printing, and presentations. You configure reports and report elements by grouping and sorting records, adding controls, and adding and modifying labels. You format a report by displaying its data in multiple columns, positioning and formatting report elements, selecting a report orientation, adding information to the report header and footer, and inserting images on the report.

This chapter guides you in studying ways of configuring report controls and formatting reports.

Exam Strategy Forms and reports have many characteristics in common, and many of the tools you use to modify the design and formatting of reports are the same as those you use to modify forms. Be sure to study “Objective group 4: Modify forms in Layout view,” for detailed information about topics such as working in Layout view, using the controls that Access offers, and modifying image layout properties. This chapter provides references to sections in Objective group 4 that describe these and other topics.

Objective 5.1: Configure report controls

This topic describes how to work with fields and controls in a report. It explains how to group and sort records, add controls, and work with labels.

Group and sort records

Specifying how records are grouped in a report is an important aspect of the report’s design. You can set grouping levels when you use the Report Wizard and by using the Group & Sort command when you work with a report in Layout view.

When you group records in Layout view, you work in the Group, Sort, And Total pane at the bottom of the report window. You can add one or more group header sections to the report based on the fields the report contains. The advantage of working in Layout view rather than Design view is that Access displays more clearly how the selections you make in the Group, Sort, And Total pane affect the report’s organization. Access doesn’t show this level of detail in Design view.

Grouped records.

You can sort records within each grouping level. For example, in a budget report, you could group records first by country or region and then sort records within that group by expense category. You could also group by expense category and sort records within that group by the date of the expense.

The More arrow in the Group, Sort, And Total pane displays additional options that you can set for grouping and sorting fields.

Additional options for grouping, sorting, and summarizing records.

The following list describes the options as Access displays them, left to right:

  • Sort Order Use this option to specify the sort order, either ascending or descending.

  • Group Interval Use this option to specify how records are grouped. You can group a text field on the first letter, for example, which would group all items that start with A together, all items that start with B, and so on. Date fields can be grouped by day, week, month, quarter, or an interval you define.

  • Totals You can add totals for multiple fields and apply different summary functions (Sum and Avg or Min and Max, for example) to the same field.

  • Title Use this option to change the title of the field being summarized. The title is used for the column heading and for labeling summary fields in headers and footers.

  • With/Without a Header Section Use this setting to add or remove the header section for each group. Access moves the grouping field to the header when you add the header section. Access prompts you to remove any controls (other than the grouping field) from the header when you remove it.

  • With/Without a Footer Section Use this setting to add or remove the footer section that follows each group. When you remove a footer section that contains controls, Access asks for confirmation to delete the controls.

  • Keep Group Together The settings for this option determine how groups are laid out on the page when the report is printed.

The options available for summarizing field values depend on the data type of the field you select. For numeric fields, the range of options include Sum, Avg, Count, Max, and Min. For text and Date/Time fields, the Count options are available—either Count Values or Count Records. For a summary report, you can use the Hide Details option in the Grouping & Totals group to show only the summary fields.

See Also As you can for forms, you can use the Order By and Order By On Load properties to change the sort order for the records in a report without changing the sort order specified in its record source. For more information, see “Sort records” in “Objective 4.2: Format forms.”

To group and sort records in a report

  1. Open the report in Layout view.

  2. On the Design tool tab, in the Grouping & Totals group, click Group & Sort.

  3. In the Group, Sort, And Total pane, do the following:

    Click Add A Group, then select the field to group by.

    Click More, then specify settings for sorting, grouping intervals, totals, title, header and footer sections, and how to keep groups together on the page.

To summarize values on a report

  1. Open the report in Layout view.

  2. In the report, select the field you want to summarize.

  3. On the Design tool tab, in the Grouping & Totals group, click Totals, then choose the summary function you want to apply.

Add controls to a report

Reports are designed primarily to present and share data (unlike forms or tables, which you use to enter, update, and delete data). For a report, you often work only with label and text box controls to identify and present data. For example, you might add a label to identify a summary field in a group header section or to provide a title in the Report Header section. You can add a text box to a report and then write an expression to create a calculated field. You can add an image control to enhance the appearance of a report. When you are adding controls in Layout view, you can expand the area of a layout by inserting rows or columns.

See Also For more information about how to work with specific controls, including how to use the control wizards, see “Objective 4.1: Configure form controls.”

Although the data is more static in a report than in a form or a table, you can use a command button to perform an action related to the report or add a hyperlink control to display a website or an email address. In Report view, command buttons and hyperlinks are operational. In print preview, Access doesn’t display a command button, and a hyperlink appears as static text.

In Design view, you can insert a subreport into a main report to provide related information. You can create the subreport by using the Report Wizard or by using the Subreport Wizard. In either case, the subreport must contain a field you can use to link it to the main report.

The Subreport Wizard appears when you add a subreport control to the report page. In the wizard, you can select a report you want to use as the subreport or select an option to base the subreport on an existing table or query.

To add controls to a report

  1. Open the report in Layout view.

  2. On the Design tool tab, click Controls. In the Controls gallery, click the type of control you want to add, then click in the report page where you want to add the control.

    The report Controls gallery.
  3. If prompted, work with the control wizard for the type of control you are adding.

To insert a subreport control

  1. Open the main report in Design view.

  2. On the Design tool tab, click Controls. In the Controls gallery, select the Subform/Subreport control, then click in the main report where you want to place the subreport.

  3. Follow the steps in the Subreport Wizard to select the report, table, or query on which to base the subreport, select fields for the subreport, and specify the field that links the subreport and the main report.

To work with control layouts in Layout view

  • On the Arrange tool tab, in the Table group, do any of the following:

    • To apply a different layout to the report, select all the fields in the report, then click Stacked or Tabular.

    • To insert a row in the layout, click a cell in the adjacent row. In the Rows & Columns group, click Select Row, then click Insert Above or Insert Below.

    • To insert a column in the layout, click a cell in the adjacent column. In the Rows & Columns group, click Select Column, then click Insert Left or Insert Right.

Add and modify labels

When you add a text, number, or date field to a report, Access creates a text box to display the field’s data and creates an associated label to display the field’s name or caption. (Access also creates an associated label for other types of fields, including lookup fields and fields that use the AutoNumber data type.) You can then use the techniques described elsewhere in this chapter to format, size, and position the labels to fit the report’s design.

See Also For more information, see “Add controls to a report” earlier in this topic and “Format report elements” in “Objective 5.2: Format reports.”

To work with the full range of properties available for a label control, open the property sheet. Specify values on the Format tab of the property sheet for properties such as Width, Height, Back Style, Special Effect, and Font Size. On the Other tab of the property sheet, you can replace the default label name.

You can also add labels to a report (or a form) that aren’t associated with fields. You might use a freestanding label to provide instructional text or to display a heading in a report.

When you add a label to the Detail section of a report, Access might display a trace error button. In the Detail section, labels in most cases are associated with controls that display data, so Access considers the addition of an independent label an error because it detects that the label is not associated with another control. You can ignore the error Access detects or select an option to create an association and then specify the field.

You can turn off the error-checking options related to labels on the Object Designers page of the Access Options dialog box.

See Also For information about setting a default format for labels, see “Manage labels” in “Objective 4.1: Configure form controls.”

To add a label

  1. Open the report in Layout view.

  2. On the Design tool tab, in the Controls group, click Controls, then click the Label control.

  3. Click in the report where you want the label to appear.

  4. Enter the text for the label.

To turn off error checking for labels

  1. Open the Access Options dialog box and display the Object Designers page.

  2. In the Error checking in form and report design view section, clear the Check For Unassociated Label And Control And Check For New Unassociated Labels check boxes.

  3. Click OK.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective5 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_5-1 database. If the Info Bar opens below the ribbon, click the Enable Content button.

  • Open the Products report in Layout view and do the following:

    • Group the report data by unit price, smallest to largest, in intervals of 10.

    • Add a total for the units in stock. Select the options to show the grand total and to show the subtotals in the group footers.

    • Add a group for the category.

    • Sort the report by unit price, smallest to largest.

    • Save and close the report.

  • Open the Sales for 2019 report in Design view and do the following:

    • Using the Controls group and the Subform/Subreport Wizard, add the Sales for 2019 Subreport as a subreport control. Insert the control in the blank space at the top of the ShippedDate Header. Note that this subreport does not need to be linked to a field in the main form.

    • Change the Sales by Year label to Sales for 2019.

    • Open the report in Report view to see your work, and then save and close the report.

  • Open the AccessExpert_5-1_results database. Compare the two databases to check your work. Then close the open databases.

Objective 5.2: Format reports

You can use a variety of tools and techniques to format a report and the controls that you include on a report. For example, you can arrange a report’s data in two or more columns, modify the control positioning, or add an image. You can also change the page orientation of the report, and you can add information to the report header and footer. This topic describes these and other aspects of formatting a report.

Format a report into multiple columns

When you want to set up a report in more than one column, you need to consider the number of fields the report contains, the width of report controls, and the page size. Columnar reports are best used for lists, directories, or other types of reports that include only a few fields. Stacking the fields (by using the stacked layout, for example) can also save space.

A three-column report.

On the Columns tab of the Page Setup dialog box, you use the Grid Settings area to specify the number of columns and the space between rows and columns. The Width and Height boxes in the Column Size area adjust column dimensions. The Same As Detail option fits the columns within the Detail section of the report. You also specify a setting for the column layout so that the data in the columns runs down the page and then across, or across the page and then down.

To specify column settings for a report

  1. Open the report in Layout view.

  2. On the Page Setup tool tab, in the Page Layout group, click Columns.

  3. On the Columns tab of the Page Setup dialog box, specify the number of columns, the row and column spacing, the column size, and the column layout option, and then click OK.

    Setting up a columnar report.
  4. Display the report in Print Preview mode to test the settings.

Modify report positioning

When a report is open in Layout view, you can use options in the Position group on the Arrange tool tab to adjust the margins around the text displayed by a control and the spacing between controls.

Your reports will be more attractive if the controls have some extra space within them. You can ensure this by adjusting the margin, which is the amount of space inside a field’s box between the border and the text. You can adjust the margins for individual fields, but reports look better if all the fields have the same margins. Each setting on the Control Margins menu—None, Narrow, Medium, and Wide—progressively increases the space between the upper-left corner of a control and the position of the control text. The Wide setting can obscure text in a text box that is less than approximately 0.3 inches in height (assuming the font size you are using is the default 11 points).

Use margins to adjust space inside controls on a report.

In a stacked or tabular layout, padding refers to the amount of space outside the box. When you adjust the padding, you change the amount of space between fields and between a field and its label. Settings in the Control Padding area (also None, Narrow, Medium, and Wide) affect the space between controls in the layout.

To specify margins for text boxes on a report in Layout view

  1. Open the report in Layout view and select the control you want to adjust.

  2. On the Arrange tool tab, in the Position group, click Control Margins, then click a margin value: None, Narrow, Medium, or Wide.

    Tip For more precise adjustments of a control’s margins, on the Design tool tab, in the Tools group, click to enable the Property Sheet command. In the Property Sheet pane, click the Format tab, then use the Top Margin, Bottom Margin, Left Margin, and Right Margin properties to set the margins for the control.

To insert padding between controls on a report in Layout view

  1. Open the report in Layout view and select the control you want to adjust.

  2. On the Arrange tool tab, in the Position group, click Control Padding,then click a padding value: None, Narrow, Medium, or Wide.

    Tip For more precise adjustments of a control’s padding, on the Design tool tab, in the Tools group, click to enable the Property Sheet command. In the Property Sheet pane, click the Format tab, then use the Top Padding, Bottom Padding, Left Padding, and Right Padding properties to set the margins for the control.

Format report elements

You can make changes to font properties for labels and other controls on a report. You can also change the size of the font, choose a different font color, or apply a background color to a control. Use the alignment buttons to position the text flush left, flush right, or centered.

Tip To select a report control for formatting, select the control from the Object list in the Selection group on the Format tool tab.

On the Format tool tab, in the Number group, you can apply a format to fields that use the Number, Currency, or Date/Time data type. The format you choose here affects how the date is displayed, but it does not change the date format specified for the field in the table.

With the commands in the Control Formatting group on the Format tool tab, you can format controls in other ways. The Shape Fill command adds a background color to a control, such as the report’s title. The Shape Outline command provides options for modifying the color and style of a control’s borders.

Each control on a report, each report section, and the report itself has a group of properties that you can work with on the property sheet to format that report element. For a text box, you can set properties such as Width, Height, Back Color, Border Style, Border Color, Font Name, Font Size, and Text Align.

Setting the Special Effect property for a text box control.

For report sections (such as Detail, Page Header, and Page Footer), you can set the Height property to 0 inches to hide the section. For the Detail section, you can set the Can Grow and Can Shrink properties to Yes if you want the size of the Detail section to increase and decrease depending on the amount of information it displays for a specific record. The Report Header and Report Footer sections also have these properties.

For the Report Header and Report Footer sections and the Page Header and Page Footer sections, you can also set the Display When property to Always, Print Only, or Screen Only. If you add page numbers to the Page Footer section, for example, set the Display When property to Print Only to show the page numbers only when you print the report.

Report properties include the Default View property, which controls whether the report opens in Print preview or Report view by default. (The report opens in the view you specify for the Default View property when you right-click a report in the Navigation Pane and then click Open.)

To select controls on a report

  • On the report, select the control you want to format.

  • On the Format tool tab, in the Selection group, do either of the following:

    • To select a specific control, expand the Object list, then click the control you want to format.

    • To select all controls on the report, click Select All.

To format report controls

  1. Open the report in Layout view and select the control or controls you want to format.

  2. On the Format tool tab, in the Font group, choose a new font, font size, or font color; apply bold, italic, or underline formatting; add a background fill color; and align the text.

  3. For number, currency, and date and time fields, use the commands in the Number group on the Format tool tab to apply number, date/time, and currency formatting to the field.

  4. In the Control Formatting group on the Format tool tab, do the following:

    • Use the Shape Fill command to add a background fill color to a control.

    • Use the Shape Outline command to apply line styles and colors to the control’s borders.

To set control and report properties

  1. Open the report in Layout view and open the property sheet.

  2. Select the control or report section you want to format.

  3. In the property sheet, click in the box for the property you want to set, then select an option Access provides or enter the value you want to use.

Change report orientation

You can switch the report layout between portrait and landscape. Portrait prints across the shorter dimension of the paper, so it is best for reports that have only a few fields. Landscape prints across the long dimension of the paper, so use it if your report has many fields.

To specify page orientation for a report

  1. Open the report in Layout view.

  2. On the Page Setup tool tab, in the Page Layout group, click Portrait or Landscape.

Or

  1. Open the report in Print Preview mode.

  2. On the Print Preview tab, in the Page Layout group, click Portrait or Landscape.

Insert information in report headers and footers

In Layout view, you can use commands in the Header/Footer group of the Design tool tab to insert standard elements in a report’s header and footer sections: a logo, a title, the date and time, and page numbers.

The Page Numbers command opens a dialog box in which you select a format, position, and alignment for page numbers. Page numbers appear in the Page Header or Page Footer section and can be centered or aligned at the left or right border. You can clear the check box for the Show Number On First Page option to start pagination on the report’s second page.

Format and configure report page number with the Page Numbers dialog box.

To insert information in a report header or footer

  1. Open the report in Layout view.

  2. On the Design tool tab, in the Header/Footer group, do any of the following:

    • To add a logo to the report header, click Logo. In the Insert Picture dialog box, navigate to and select the logo image file, then click Open.

    • To add a title to the report header, click Title. In the Auto_Header() control that appears, replace the default title with the report title you want.

    • To add the date or time to the report header, click Date And Time. In the Date and Time dialog box, select the check boxes for the elements you want to include, select the element formats you want, and then click OK.

    • To add page numbers to the report header or footer, click Page Numbers. In the Page Numbers dialog box, click the format, position, and alignment you want. Then click OK.

Insert images on reports

Reports can display logos or images related to the purpose of the database—product thumbnails, project locations, or employee portraits, for example. You can also insert an image as the background for a report. Access offers quite a few properties that affect the appearance and behavior of the image.

See Also For a complete description of the properties that you can configure for an image, see “Insert images” in “Objective 4.2: Format forms.”

To insert an image on a report

  1. Open the report in Layout view.

  2. On the Design tool tab, in the Controlsgroup, click Insert Image, then do either of the following:

    • Select an image in the Image gallery.

    • Click Browse to locate the image file you want to use. Select the image file in the Insert Picture dialog box, then click OK.

  3. Click the report at the position where you want the image to appear.

  4. Open the report property sheet. At the top of the property sheet, in the Selection Type list, click the image name.

  5. On the Format tab of the property sheet, set the values you want to use for the following properties: Picture Type, Picture, Picture Tiling, Picture Alignment, and Picture Size Mode.

To add an image to a report’s background

  1. Open the report in Layout view.

  2. On the Format tool tab, in the Background group, click Background Image, then do either of the following:

    • Select an image in the Image gallery.

    • Click Browse to locate the image file you want to use. Select the image file in the Insert Picture dialog box, then click OK.

  3. Open the report property sheet. At the top of the property sheet, in the Selection Type list, click Report.

  4. On the Format tab of the property sheet, set the values you want to use for the following properties: Picture Type, Picture, Picture Tiling, Picture Alignment, and Picture Size Mode.

The practice file for these tasks is located in the MOSAccessExpert2019\Objective5 practice file folder. The folder also contains a result file that you can use to check your work.

  • Open the AccessExpert_5-2 database. If the Info Bar opens below the ribbon, click the Enable Content button.

  • Open the Products report in Layout view and do the following:

    • Set the control margins to Wide for all the controls.

    • Adjust the height of all the controls to 0.31 inches.

    • Add the date and time to the Header section of the form. In the report’s property sheet, select the formats Medium Date and Medium Time, respectively.

    • Change the report page orientation to Landscape.

    • Set up a three-column report and set the column width to 3 inches.

    • Arrange the fields to go down, and then across.

    • Apply bold formatting to all the report labels.

    • Select the Products Report label in the report header, apply bold and italic formatting, change the Font to Calibri Light, and set the font color to Dark Blue.

    • For the Products Report label, set the Special Effect property to Shadowed.

    • Display the report in print preview, then save and close the report.

  • Open the AccessExpert_5-2_results database. Compare the two databases to check your work, then close the open databases.