Home > Sample chapters > Microsoft Office > Access

Create Simple Reports in Microsoft Access 2010

Modifying Report Design

You can use the Report wizard to get a quick start on a report, but you will frequently want to modify the report to get the result you need. As with forms, the report consists of text box controls that are bound to the corresponding fields in the underlying table and their associated labels. You can add labels, text boxes, images, and other controls, and you can format them, either by using commands on the ribbon or by setting their properties in the report’s Property Sheet.

You can adjust the layout and content of reports in either Layout view or Design view. For simple adjustments, it is easier to work in Layout view, where you can see the layout with live data, making the process more intuitive.

In this exercise, you’ll modify the layout of a report. You’ll then apply a theme, change some of the colors, and dress up the text with character formatting. You will also apply a simple rule that formats values differently if they meet a specific criterion.

  1. In the Navigation pane, under Reports, right-click the Alphabetical List of Products report, and then click Print Preview.

  2. Maximize the program window if it isn’t already maximized, and then point to the previewed report page.

    The pointer changes to a magnifying glass with a plus sign in it.

    httpatomoreillycomsourcemspimages1745037.jpg

    In Print Preview, the magnifying glass pointer indicates that you can zoom in on the page.

  3. Click the previewed page once to zoom in.

    Notice that the report has the following design problems:

    • Extraneous text

    • Spacey arrangement

    • Uninviting formatting

    To fix these problems, we need to switch to Layout view.

  4. On the View Shortcuts toolbar, click the Layout View button.

    httpatomoreillycomsourcemspimages1745039.png

    Access adds four Report Layout Tools contextual tabs to the ribbon. First let’s work with the group header controls.

  5. On the Design contextual tab, in the Grouping & Totals group, click the Hide Details button.

    httpatomoreillycomsourcemspimages1745041.jpg

    The controls that are bound to fields in the Products table are hidden so that you can concentrate on the group header controls.

  6. Below the title, click ProductName by 1s, and press the Delete key.

    The label is removed from all the group headers.

  7. Click the control containing A, and drag it to the left edge of the header.

    When you release the mouse button, all the corresponding controls move to the corresponding location in their own group headers.

  8. Point to the right border of the selected A control, and when the pointer changes to a double-headed arrow, drag to the left until the control is just big enough to hold its contents.

    Again, all the corresponding controls assume the new size.

    httpatomoreillycomsourcemspimages1745043.jpg

    It is easier to work with the controls in the group header when the report details are hidden.

  9. With the A control still selected, hold down the Shift key, and in turn, click the Product Name, Quantity Per Unit, and Units In Stock label controls to add them to the selection.

  10. On the Design tab, in the Tools group, click the Property Sheet button.

    httpatomoreillycomsourcemspimages1744935.jpg

    The Property Sheet opens.

    httpatomoreillycomsourcemspimages1745045.jpg

    Because more than one control is selected, the Selection Type of this Property Sheet is Multiple Selection.

  11. On the Format page of the Property Sheet, in the Top property box, type 0.25″, and press Enter. Then close the Property Sheet.

    In the group header, the letter control and label controls are now aligned 0.25 inch from the top of the header, and the height of the header has decreased because less space is needed to accommodate the controls.

    Now let’s see how the group header looks with its data.

  12. In the Grouping & Totals group, click the Hide Details button to turn it off and display the data from the table.

    The numbers in the Units In Stock column are right-aligned. Let’s center them.

  13. Click the first text box control under the Units In Stock label, and on the Format contextual tab, in the Font group, click the Center button.

    httpatomoreillycomsourcemspimages1745047.jpg

    Now we’ll add some color and format the text.

  14. On the Design contextual tab, in the Themes group, click the Themes button, and in the gallery, click the Austin thumbnail.

    httpatomoreillycomsourcemspimages1744925.jpg

    Although nothing much appears to change, the report takes on the color scheme and font scheme assigned to the selected theme.

  15. Inside the shaded area of the report header, but away from the title, click a blank area. On the Format contextual tab, in the Control Formatting group, click the Shape Fill button. Then under Theme Colors in the palette, click the third box (Light Green, Background 2).

    httpatomoreillycomsourcemspimages1745049.jpg

    When you created this report, alternate group headers were shaded with the same color as alternate data rows. This coloring confuses rather than clarifies the report structure. Let’s turn off this alternate group header color.

  16. Click outside the dotted border to the left of the first group header. In the Background group, click the Alternate Row Color arrow, and at the bottom of the palette, click No Color.

    httpatomoreillycomsourcemspimages1745051.jpg
  17. In the Control Formatting group, click the Shape Fill button, and in the palette, click a light brown color.

    The entire group header is shaded except the alphabet controls (A, B, C, and so on) that you moved earlier. If you wanted to shade them as well, you could select one of them and repeat step 17 to apply the light brown fill.

  18. Click outside the dotted border to the left of the first row of data in the report, and remove the alternate row color of the data rows. Then click the white space above the report header to see the result.

    Only the backgrounds of the report header and group headers are now colored.

    httpatomoreillycomsourcemspimages1745053.jpg

    Removing the alternate row color makes the structure of this report more obvious.

  19. Click any control, and then in the Selection group, click the Select All button.

    httpatomoreillycomsourcemspimages1745055.jpg
  20. In the Font group, click the Font Size arrow, and then click 9.

  21. Click the report’s title control, and then use the commands in the Font group to make the text 24 points, bold, and dark green.

  22. Select the controls in the group header, and make them bold and dark green.

  23. Scroll down the report, noticing that a few of the values in the Units In Stock column are 0.

    We want these values to stand out in the report to remind buyers that it is time to order more of these products.

  24. Click any control in the Units In Stock column. Then in the Control Formatting group, click the Conditional Formatting button.

    The Conditional Formatting Rules Manager dialog box opens.

    httpatomoreillycomsourcemspimages1745057.jpg
  25. Click New Rule.

    The New Formatting Rule dialog box opens.

    httpatomoreillycomsourcemspimages1745059.jpg

    You can create rules that compare the current field value to a specific value or to other values in the same field.

  26. With Check values in the current record or use an expression selected as the rule type, in the Format only cells where the area, click the arrow for the second box, and click less than. Then in the third box, type 1.

  27. In the bottom area, click the Bold button, and change the Font color setting to red. Then click OK.

    In the Conditional Formatting Rules Manager dialog box, the rule is listed in the Rule column with the formatting that will be applied to values that meet the rule’s criteria in the Format column.

    httpatomoreillycomsourcemspimages1745061.jpg

    Values that are less than 1 will be bold and red.

  28. Click OK. Then scroll down the report again, noticing that all the 0 values are now bold and red.

  29. Close the report, clicking Yes to save your changes to its design.