Section 11.1. Improving Reports in Design View

11.1. Improving Reports in Design View

Design view is the secret to setting your reports free. As you learned in the previous chapter, Design view's another way of looking at your report. Unlike Layout view, in Design view you don't see any report data. Instead, you see a blueprint that tells Access how to create the report. Using this blueprint, you can do things that just aren't possible in any other view.

Consider the simple report with the list of products that you created in the previous chapter. If you switch to Design view, then you see what makes this report tick (Figure 11-1). To get to Design view, right-click the tab title, and then choose Design view.

Figure 11-1. The Design view window's divided into five sections. Each section tells Access how to construct part of the report. The Detail section's the most important part. When you fire up the report (by printing it or viewing it in layout mode), Access repeats the detail section once for each row. It fills in the boxes in the Detail view with the values from the corresponding record.

Note: The odd grid of lines and dots you see in Design view is intended to help you line up different parts of your report. As you'll see, people often use Design view to place data in precise locations, and there's no automatic feature to line up different parts of your report.

11.1.1. The Design View Sections

The secret to mastering Design view is understanding its five different sections. Although you can leave some sections blank, every report includes them in exactly the same order:

  • Report Header . This section appears once at the beginning of your report, on the first page. This section's where you add titles, logos, and your own personal byline.

  • Page Header . This section appears just under the report header on the first page, and at the top of each subsequent page. It's the place to add page numbers , and you can also use it for column headers in simple, tabular reports like the product catalog.

  • Detail . This section appears once immediately after the page header, and it's the heart of all reports. The trick's that the detail section's repeated once for each record in your report. In a simple tabular report, this section represents a single row.

  • Page Footer . This section appears at the bottom of each page. If you don't use the page header for page numbers, then this section provides your other option.

  • Report Footer . This section appears once at the very end of the report. You can use it to print summary information, copyright statements, the date of printing, and other miscellanea.

These sections' content looks a fair bit different from what you see in other views, because it doesn't show the live data. Instead, it includes placeholders where Access can insert the necessary information each time it runs the report. When you run the product report, Access grabs the values from the ProductCategoryID, Product- Name , Price, and Description fields, and then shuffles them into the matching boxes.

You'll need a bit of time before you're comfortable manipulating the content in Design view. First, you need to learn that you can adjust each section's size . This ability makes sense, because different reports allocate different amounts of space to each region. Figure 11-2 shows how this works.

Figure 11-2. To resize a section, move your mouse to the border just underneath the section you want to change. Drag the border down (to add more space) or up (to remove space). It's then up to you to shuffle around the content inside to fit the available space. In this example, the Details section's being heightened.

Note: If you don't want to use a section, then you can resize it almost out of existence. Look at the report footer section in Figure 11-1. It's there, but vanishingly small, because this report doesn't use the footer. Alternately, you can hide the header and footer section. Just right-click the report, and then choose Page Header/Footer to hide the page header and page footer sections, or Report Header/Footer to hide the report header and footer sections. (Click these commands again to make these sections reappear.)

11.1.2. Understanding Controls

Design view gives you a different perspective for your report. Access represents everything in your report with controls : graphical widgets that have text, pictures, and formatting. Each control's a distinct element. You can change what it looks like, or you can drag it to a different position (although you may need to pull it out of its layout table first, as described in Section 11.1.3).

Figure 11-3 points out the controls in the product catalog report. You don't see all the controls, because not all the columns fit into view at once.

Figure 11-3. In the product report, you'll find controls for the title, page number information, column headers, and data. Each control has a thin black border around it, which helps you spot it and resize it in Design view. These borders won't appear in the printout.

Note: Something may strike you as a little odd in Figure 11-3. Namely, there are several text box controls that look more like label controls. These controls' content causes this phenomenon . As you'll learn later in this chapter, reports use label controls for text that never changes, and text box controls for text that does (based on the current date, the current record, the current page, and so on).

To get a little more comfortable in Design view, play around with a simple report. (If you don't have a report handy, then you can download the Boutique Fudge database with the examples for this chapter; see the "Missing CD" page at Here are some tasks to try out:

  • Drag the controls in the report header and page footer sections from one place to another.

  • Change the size of a control in the report header or page footer section by dragging the black border that appears around it.

  • Rearrange the columns by dragging the column headers around the page header section, or by dragging the fields around the detail section. When you move a column, Access automatically arranges the other columns around it. Access works the same way when you rearrange the columns in Layout view.

  • Select a control, and then change its formatting using the ribbon's Report Design Tools Design Font section. This method works with the elements in the page header and footer sections as well as the column headers and individual fields.

When you're finished making a change, right-click the tab title, and then choose another view (like Report view, Layout view, or Print Preview) to see what your printed report will look like. When you close your report, Access prompts you to save the changes you've made.

You should be able to do everything you did to customize a report in Layout view using Design view. Of course, Layout view's easier to use for most of these tasks. But as you'll see in the following section, Design view gives you more freedom to break out of the typical report table and arrange your data however you want.

11.1.3. Moving Fields out of a Layout

In a simple report, Access groups all the fields into something called a layout . The layout's actually a specialized container that lets you easily work with groups of fields. It's new in Access 2007, and it provides several indispensable conveniences that you saw in Chapter 10:

  • When you move a column header, the column data moves with it, and vice versa.

  • When you move a column to a new position, Access rearranges all the other columns accordingly .

  • When you widen a column, Access bumps all the following columns out of the way. Similarly, when you shrink a column, the following columns move to fill the extra space.

Without a layout, you couldn't move your columns around as quickly. Every time you wanted to make a change in one column, you'd need to painstakingly reposition every other column. In a report with several dozen fields, this process adds up to a major headache .

Although layouts are small miracles of convenience, they're also a bit of a straight-jacket if you want to arrange your data differently. Suppose you want to take the product catalog report and make it look less like an inventory list and more like a retail publication, as shown in Figure 11-4. You can't do this with a layout, because your fields are always locked into a tight tabular structure. You can only get this result if you take your fields out of their layout table, and then arrange them by hand.

Note: Don't get confused between layouts and Layout view . A layout's a container that arranges a bunch of controls. Layout view's a way to look at your report and change various aspects of it. You can use Layout view even if you don't use any layout containers.

Figure 11-4. This version of the product catalog report doesn't use a layout tableinstead, all the controls are free-floating. Access still creates the report in the same way as it does when you use a layout tableit repeats the detail section once for each row. The only difference is the way Access arranges information in the detail section.

Here's how to transform the layout-based product catalog into the table-free version shown in Figure 11-4:

  1. Switch to Layout view (right-click the tab title, and then choose Layout View) .

    You can remove fields in Design view, but the results aren't as pleasant. When you remove a field in Layout view, Access automatically bumps it aside and gives it a little space of its own. But when you remove a field in Design view, Access leaves it in its original position. Because the layout table's still underneath this field, you end up with controls that are bunched up on top of each other, which makes them very difficult to arrange.

  2. Find the field that you want to remove from the layout. Right-click the column header, and then choose Layout Remove .

    Repeat this step to remove all the fields you want to arrange. You decide whether you want to mix and match by putting some fields in a layout and keeping others layout-free, or if you want to remove all your fields from the layout table (as in Figure 11-4).

    To remove several fields at once, hold down Shift while you click several columns, and then choose Layout Remove. (Or click the tiny four-way-arrow icon that appears in the reports top-right corner to select every column.)

  3. Now you'll see a jumble of fields on your report. Right-click the tab title, and then choose Design view .

    You could arrange your fields in Layout view, but most people find this tricky because Layout view shows several records at a time. Instead, you'll probably have an easier time arranging them using the section templates in Design view.

  4. Make the detail section larger by dragging down the bottom edge (as shown in Figure 11-2) .

    In a simple report, you need exactly one row to fit your record. But when you create a custom arrangement, you almost always need more space.

  5. Drag the text box control for each field to the right place in the detail section, and then resize it to the right size .

    It may take some rearranging before you finally get all the boxes in the right place. Since the information isn't in a layout table anymore, Access doesn't automatically move information out of the way. Instead, you need to arrange everything by hand and make sure no two fields overlap.

  6. In the page header section, select a column header for one of the fields in the detail section. Either press Delete to remove it, or drag it down into the detail section .

    It makes no sense to include a column header at the top of a page when your field isn't a part of the table any longer. If the data's fairly self-explanatory, you don't need a caption at all. However, you could also drag the column header into the detail section, and place it next to the corresponding data, so that it acts like a caption. The report in Figure 11-4 keeps the captions for the UnitsInStock and UnitsOnOrder fields.

  7. If you haven't already, select each field, and then apply the formatting you want .

    You can format the data in Design view in much the same way you do in Layout view. Just select the field, and then use the ribbon's Report Design Tools Design Font section. Hold down Shift if you want to select (and then format) several controls at a time.

    When you're finished, switch to Layout view or Report view to see the result of your changes. Figure 11-5 shows the final arrangement for the revamped product catalog report shown in Figure 11-4.

11.1.4. Adding More Controls

In the previous example, you used your knowledge of controls to unshackle the fields in your report. However, text boxes with field values aren't the only type of control you can use. Access reports also support labels, pictures, buttons and other graphical gizmos that can jazz up the dullest report. Some of the reasons you may add more controls include:

  • To add more text information, like subtitles , disclaimers, explanatory notes, the company name, and so on.

  • To draw separating lines between regions in the detail section.

    Figure 11-5. Once you've removed the fields from their layout table, you can arrange them any way you wanteven letting one control overlap another for a tighter display.

  • To draw additional borders around important content.

  • To pop a logo into the header or footer. (The automatic logos you learned about in Section 10.1.5 are limited to the report header.)

You can easily add a few more controls to your report. Just find the right button on the ribbon. When your report's in Design view, you'll find one-stop shopping in the Report Design Tools Design Controls section, as shown in Figure 11-6.

Figure 11-6. Using the buttons on the left of the Controls section, you can add a few familiar ingredients , like the report title and page numbers. When you click the appropriate button, Access automatically inserts the corresponding element where it belongs. The buttons in the middle are more specialized. To add one of these controls, you need to click the button, and then draw the control onto the design surface at the right location. Once you've added your control, the buttons on the right let you add a border around it.

Some of these controlslike text boxes, checkboxes, and other editing controlsare really intended for use in forms, and aren't much use in a report. Others, like buttons and hyperlinks , can trigger useful actions when combined with a dash of macro code (as you'll see in Chapter 15). But right now, you'll want to use only a few controls:

  • The label control holds small or large amounts of fixed text. For example, all the column headers with the field names are labels.

  • The text box control holds a dynamic expressionin other words, text that can change.

  • The image control holds a picture.

  • The line control lets you draw vertical, horizontal, and even diagonal lines. It's handy when you want to separate content graphically, and borders alone don't give you the effect you want.

  • The rectangle control lets you draw formatted rectangles around other controls to help content stand out.

  • The page break control lets you split the detail section into separate pagesexactly where you want. It's useful when there's a large amount of information in the detail section, or when you're printing forms that need to wind up on separate pages (like invoices for different customers).

Note: Although Access has controls for pictures, rectangles, and lines, it doesn't have the Clip Art features you find in other Office applications. So don't look around for fancy shapes and word artyou won't find them.

Once you've picked the control you want, you can add it your report, as shown in Figure 11-7.

Figure 11-7. Top: To add a control to your report, click it in the toolbar. Then, drag the design surface until the rectangle covers the area where you want to place the control.
Bottom: When you release the mouse button, the control appears in the rectangle. Of course, you can move or resize the control after the fact to get it just right. If you're adding a label (as shown here), you need to follow up by typing in your text.

If you're adding a label, you'll want to set the text that appears inside. Once you add the label, the cursor appears inside so you can enter some text. If you want to edit the text in a label later on, click once to select the label, and then pause until the mouse pointer changes into a text pointer (known to techies as an I-beam). Then, click again in the label to start editing its text.

Note: When you add a new label, Access may pop up an exclamation-mark icon. If you hover over the exclamation mark, then you see a warning that tells you your label isn't linked to any other control (like a text box with the value of a field). Don't worry, if you're just adding a basic title or some unchanging bit of text, this situation's exactly what you want.

If you're adding a rectangle, you probably want to set both the line color (using the Report Design Tools Design Controls section) and the background fill color (using the Report Design Tools Design Font section). If you put two controls in the same place, then Access stacks the control that you added most recently on top of the control you added first. To move a control into the background, select it, and then choose Report Design Tools Arrange Position Send to Back.

11.1.5. Creating a Report from Scratch (in Design View)

So far, you've tried your hand at modifying a simple report using Design view. But if you don't want to use a layout table, then it's easier to start out in Design view and build your report there. When you build a report in Design view, Access doesn't automatically add your fields to a layout, as it does in Layout view.

To create a report in Design view, you simply need to create a new, blank report, and then add all the controls you need to the appropriate sections. The following steps walk you through the process:

  1. Choose Create Reports Report Design .

    This action creates a new, blank report, and then opens it in Design view.

  2. Choose Report Design Tools Design Tools Add Existing Fields .

    The Field List pane appears at the right of the window, with a list of tables and the fields they contain.

  3. Drag the fields you want to show in your report from the Field List into the detail section .

    Each time you drop a field onto your report, Access adds two controls: a label that shows the field name and a text box that displays the field data (see Figure 11-8).

    Note: Access needs to use the text box control instead of the label control to display field values, because the label control's limited to fixed, unchanging text. Only the text box can get live values from a field.
  4. Move the field to the right place, and then resize it to the right size .

    Resizing can be a bit tricky at first, because you're working with two linked controls. If you drag either piece, then the other moves along with it. Figure 11-9 shows how to move just the caption or just the field value.

    If you don't want the label at all, then just select it, and then press Delete.

  5. Add additional content (like a title, page numbers, and miscellaneous text and pictures) using the ribbon's Report Design Tools Design Controls section .

    The Controls and Fields section lets you insert a wide range of different controls, as described in Section 11.1.4.

    Figure 11-8. Here the ProductName field's been added to a new report. As you add the fields you want, you'll need to spend considerable time moving them around the design surface until the report looks right.

    Figure 11-9. Depending on where you click, you can move both the field caption and field value at once, or you can move just one piece. You can also resize either part by dragging the left or right edge.

  6. Apply any formatting you want to your controls .

    The Report Design Tools Design Font section has the commands you need to change the typeface, text size, and colors, while the Report Design Tools Design Controls section has the commands needed to add borders around a control.

    You can format field values and field captions separatelyjust make sure you select the right part before you click the formatting command.

    Tip: You can use conditional formatting to make certain values stand out, just like you did in Layout view (Section 10.3.2).
  7. If you want to use a report header or footer, then right-click a blank space on the design surface, and then choose Report Header/Footer .

    This action makes the report header and report footer sections appear. You can then add controls to these sections. Repeat this step to hide the report and footer if you decide you don't need them.

    You can also hide the page header and footer sections by right-clicking the design surface, and then choosing Page Header/Footer.

  8. Resize the report sections so they don't have extra blank space at the bottom .

    Typically, you'll need to shrink the detail section, because it starts out being quite large. If you don't, then you'll have considerable blank space between each record in your report.

  9. Save your report .

    You can save your report at any time by going to the Office menu, and then choosing File Save, or you can close your report, at which point Access prompts you to save it.

Access 2007[c] The Missing Manual
Access 2007[c] The Missing Manual
ISBN: 596527608
Year: 2007
Pages: 153 Ā© 2008-2017.
If you may any questions please contact us: