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:

The content in these sections looks a fair bit different from what you see in other views, because Design view doesn’t show the live data. Instead, it includes placeholders where Access inserts the necessary information each time it runs the report. When you run the product report, for example, Access grabs the values from the ProductCategoryID, ProductName, 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 Detail section is being heightened.

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 is 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 on Moving Fields Out of a Layout).

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.

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

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 by using Design view. Of course, Layout view is 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.

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

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 straitjacket 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.

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

In the following steps, you’ll learn how to transform the layout-based product catalog into the table-free version shown in Figure 11-4. You’ll begin in Layout view, where you can remove the fields from their layout. Then you’ll switch into Design view to customize the arrangement of your fields. (Word of advice: Once you know your way around Design view, you’ll probably get the job done quicker by creating a new, layout-free report from scratch in Design view, as described on Creating a Report from Scratch (in Design View).)

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

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:

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

Figure 11-6. The buttons in the Controls section let you add all sorts of useful widgets to your reports. To add one of these controls, click the button, and then draw the control onto the design surface at the desired location. Using the buttons to the right of the Controls section, you can add a few more ingredients to each page of your report, like a report title and page numbers. When you click one of these buttons, Access automatically inserts the corresponding element where it belongs.

Some of these controls—like text boxes, checkboxes, and other editing controls—are 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:

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.

If you’re adding a rectangle, you probably want to set both the line color and the background fill color (using the Shape Outline and Shape Fill buttons in the Report Design Tools | Format→Control Formatting 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→Sizing & Ordering→Send to Back.

You can also give some controls (especially text boxes and labels) a transparent background, so the content underneath them shows through. To try it out, select the control and choose Report Design Tools | Format→Control Formatting→Shape Fill→Transparent.

So far, you’ve tried your hand at modifying a simple report by 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:

If you’re still a little overwhelmed by the Property Sheet, try the following steps. They walk you through the process of making a change:

You can also use this technique to set the background for an entire section of the report. From the drop-down list in the Property Sheet, just choose a section like ReportHeader or Detail, and then follow these steps.

In this example, you could change the background color more easily using the ribbon. However, you can change many properties in the Property Sheet that have no equivalent in the ribbon. Table 11-1 lists a few useful examples, all of which you find in the Format tab.

To create groups, switch to Layout view or Design view. Then, find the field you want to use for grouping. For example, if you want to group a product list by product category, you need to group on the ProductCategoryID field. (Or, if you’ve added a related field to your report from the ProductCategories table, like CategoryName, you can use that instead—it’s the same thing.)

To apply your grouping, right-click the field you want to use for grouping and then select Group On. Access sorts your results by that field, and then groups them. Figure 11-18 and Figure 11-19 show two reports that group products by category.

Figure 11-18. Here, a simple tabular report is sorted and grouped by category with a few quick clicks.

Grouping works by adding more sections to your report. If you group using the ProductCategoryID field, your report gains a new section named ProductCategoryID Header, which Access places just above the Detail section (see Figure 11-20). This group header includes information about the grouping—in this case, the product category. The Detail section has the data for each record that’s placed in the group.

When you use grouping, it may not make sense to keep your column headers in the page header section. That’s because every group header interrupts your table. Often, you’re better off placing the column headers at the bottom of the group header, so they appear at the beginning of every group (not at the top of every page). Figure 11-18 uses this approach. (Figure 11-19 doesn’t need to, because it doesn’t use any headers at all.) Unfortunately, to use this more attractive arrangement, you need to remove your fields from their automatic layout (Moving Fields Out of a Layout).

Figure 11-19. Grouping works equally well with reports that have complex, non-tabular layouts. However, you could have a slightly harder time seeing where the groups begin and end, so consider giving the category section a different background color (using the Back Color property described on page 367) to make it stand out, as in this example. Or, you can use the line control to create a divider at the top of each category. Figure 11-20 shows this report in Design view.

Figure 11-20. Design view provides the easiest way to add content to the header section of each group. In the ProductCatalog report, you may want to add additional fields from the ProductCategories table to the ProductCategoryID Header section (like the Description).

Once you have your grouping in place, you have many more options:

You can most easily add any of these features with the “Group, Sort, and Total” pane. To show it in Design view, choose Report Design Tools | Design→Grouping & Totals→Group & Sort. Or, in Layout view, choose Report Layout Tools | Design→Grouping & Totals→Group & Sort.

The “Group, Sort, and Total” pane appears at the bottom of the window. Figure 11-21 shows what you’ll see if you examine the products-by-category report from Figure 11-19.

Figure 11-21. This report has one level of sorting (alphabetically by CategoryName) and one level of grouping (byProductCategoryID). To see more options for any given level, select it and then click More (circled). Figure 11-22 shows the grouping settings you can change.

The following sections describe your options in the “Group, Sort, and Total” pane:

Sort by

Chooses the field that’s used for sorting. In Figure 11-22, fields are sorted by CategoryName, and then grouped by ProductCategoryID.

Figure 11-22. The “Group, Sort, and Total” pane gives you a quick way to set up subtotals, headers and footers, and page break options for each level of grouping you use.

By Entire Value

Tells Access to create a separate group for every different value in the grouped field. If you’re grouping by ProductCategoryID, this option ensures that Access places each category in a distinct group. In some situations, this approach creates too many groups, making it difficult to perform any analysis (and wasting reams of paper). In cases like that, you need a way to create larger groups that include more records. If you’re grouping products by price, or orders by date, then you may prefer to group a range of values, as shown in Figure 11-23.

Figure 11-23. With date or numeric fields, you can choose to create a group that encompasses an entire range of values. In this example, groups are created by price in intervals of five. The first group has products priced less than $5, the next group stretches from $5 to $9.99, and so on.

With … Totaled

Subtotals is the most popular grouping feature. Subtotals let you compare how different groups stack up to one another. The “Group, Sort, and Total” pane lets you perform calculations using any numeric fields for your subtotals (Figure 11-24).

Figure 11-24. In this example, the list of products is grouped by category, and the average price of each category is displayed in the footer.

Depending on what you’re trying to accomplish, you can count values, add them, calculate averages, or determine maximum and minimum values in a group. You can place this information into a header that appears at the beginning of each group or into a footer that follows at the end. Finally, you can top your report off with a final grand total that adds up all the subgroups.

Keep Group Together on One Page

This setting helps you prevent orphaned category headers. In the product catalog example, this option makes sure you don’t wind up with a group title like Beverages at the bottom of a page, and all the matching products on the following page.

Ordinarily, Access doesn’t prevent awkward page breaks. Instead, it simply tries to fill each page. If this isn’t what you want, then you have two other choices. You can choose to make sure the entire group is always placed on the same page (assuming it’s less than one page long), or you can choose to make sure the header and at least one record are kept together on the same page.

One option the “Group, Sort, and Total” pane doesn’t offer you is forcing a new page break at the beginning of each group. To accomplish this, you need to switch to Design view, select the group header section, and then, in the Format section Property Sheet, look for the Force New Page setting. Set it to Before Section to force a page break at the beginning of each new section, or After Section to force the page break at the end of the section. (You don’t see a difference between these two settings unless you’re using a report header and footer. If you have a report header and you use Before Section, you end up with a page break between the report header and the first section.)

Note

You don’t see Force New Page setting’s effect in Layout view, Report view, or Design view. It appears only when you use the Print Preview feature, or when you actually print your report.

In many scenarios, you’ll want a group to start on a new page. This stipulation makes sense when printing the class list shown in Figure 11-25. In this case, the Force New Page setting lets you avoid putting two attendance lists on the same page.

Figure 11-25. This class attendance list uses several of the tricks you’ve learned in this chapter to create a printout that’s a world away from a typical report. The report displays a list of students grouped by class. There’s no report header, but the group header for each class adds a few key pieces of information from the Classes table. Each class group starts on a new page thanks to the Force New Page settings, and each student is followed by a row of hand-drawn rectangle controls where you can tick off the attendance.

Your reports aren’t limited to a single group. In fact, you can add as many levels of sorting and grouping as you want, to slice and dice your data into smaller, more tightly focused subgroups.

To add another level of grouping, just right-click the field you want to use, and then click Group On. This adds it to the list in the “Group, Sort, and Total” pane. (You can also add additional levels of sorting by right-clicking a field, and then choosing a sort command. If you began by sorting and grouping your products into categories, you could sort each category by product name.)

When you have more than one group in the “Group, Sort, and Total” pane, it’s important to make sure they’re applied in the right order. For example, if you want to create a list of all the items that each customer has ordered, you need to group first by order (to group together all the items in each order) and then by customer (to assemble all the orders made by each customer). If you group first by customer and then by order, you’ll get a nonsensical report that attempts to subdivide each order into customer-specific groups. And unless your business lets customers join together to make orders, this arrangement is meaningless.

To change the order of your groups, select one of the grouping levels in the “Group, Sort, and Total” pane and then click the up or down arrow button to move it (Figure 11-26). To remove a grouping level altogether, select it and then press Delete.

Figure 11-26. Access applies groups in a top-to-bottom order. So in this example, the results are grouped by CustomerID, and then by OrderID, which is what you want. The result is a list of orders for each customer Figure 11-27).

Figure 11-27. This report transforms order information into a printable invoice by using grouping and a few other tricks you’ve seen already. The raw information comes from the OrderDetails table (supplemented with information from the Orders, Product, and Customer tables). Line controls separate the different sections of the invoice, automatically generated totals tell you how much the order costs altogether, and expressions combine a few fields and add some extra text (like “Order History for” and “Order #”). This example is included in the Boutique Fudge database with the downloadable content for this chapter.

Each group you add can have a header and footer section and its own set of totals. You add these ingredients by using the “Group, Sort, and Total” pane—just select the appropriate grouping level, click More, and then change the various options, as described in the previous section.

When you have more than one level of grouping, you can hide some of your information so you see just the totals. In Layout view, simply choose Report Layout Tools | Format→Grouping & Totals→Hide Details. If you use this technique on the example in Figure 11-27, Access hides the line-by-line order details, and all you’ll see is the total for each order.