Recipe 3.3 Create a Report with Multiple Columns

3.3.1 Problem

You want to print a two-column, phone-book-style report with large initial capital letters to set off each alphabetical grouping. There is no Report Wizard for creating such a report, and you don't see a Column property to set up the number of columns you want. How can you make a multiple-column report in Access?

3.3.2 Solution

There is a way to format a report for multiple columns, but it's not where you might look for it, on a report's properties sheet or the report design menu. Instead, you'll find it on the Columns tab of the Page Setup dialog. This solution guides you through setting up a multiple-column, phone-book-style report that includes a large drop cap for each letter of the alphabet.

Follow these steps to create your own multiple-column report:

  1. Open in design view the report you want to format for multiple columns, and select File Page Setup. The Page Setup dialog appears. Click on the Columns tab of the Page Setup dialog (see Figure 3-5).

Figure 3-5. The Columns tab of the Page Setup dialog
figs/acb2_0305.gif
  1. Enter the appropriate settings for your report. You'll find a brief description of these settings and the settings used for the sample report in Table 3-3. Click OK when you're done.

Table 3-3. The Page Setup dialog Layout settings

Setting

Purpose

Sample

Number of Columns

Number of columns.

2

Row Spacing

Extra space, in inches, between rows.

0

Column Spacing

Extra space, in inches, between columns.

0.25"

Column Size: Width

Width of each column.

3"

Column Size: Height

Height of each column.

1.0625"

Same as Detail

When you check this, Access will copy the width and height of the report's detail section into the Width and Height controls.

Unchecked

Layout Column

Select "Down, then Across" for snaking columns or "Across, then Down" for mailing-label-style columns.

Down, then Across

  1. Leave the report and page headers and footers as they are (if your report has these sections); they will still print across the entire report width.

  2. To keep each name, phone number, and address from breaking inappropriately, set the detail section's KeepTogether property to Yes.

  3. Preview the report; it should now display in two columns.

Follow these additional steps to create the first letter grouping shown in Figure 3-7:

  1. Select View Sorting and Grouping to display the Sorting and Grouping window. Add the grouping field (in rptPhoneBook, we grouped on Company) twice to the Sorting and Grouping grid. Adjust the settings of each grouping field as shown in Table 3-4 for the sample report.

Table 3-4. Sorting and Grouping settings for rptPhoneBook

Setting

First Company field

Second Company field

Field/Expression

Company

Company

Sort Order

Ascending

Ascending

Group Header

Yes

No

Group Footer

No

No

Group On

Prefix Characters

Each Value

Group Interval

1

1

Keep Together

No

No

  1. Add a text box to the header section of the grouping field. In the rptPhoneBook report, we used the property settings in Table 3-5. The completed rptPhoneBook report is shown in design view, with the Sorting and Grouping and properties sheets visible, in Figure 3-6.

Table 3-5. Property settings for rptPhoneBook

Property

Setting

Name

txtFirstLetter

ControlSource

=Left([Company],1)

Width

0.4375"

Height

0.4375"

BackColor

12632256 (grey)

ForeColor

0 (black)

FontName

Arial

FontSize

24

FontWeight

Bold

Figure 3-6. The completed rptPhoneBook report in design view
figs/acb2_0306.gif
  1. Save the report. Switch to print preview mode to preview how it will look when you print it.

Now, load 03-03.MDB. The tblCompanyAddresses table contains a list of businesses and their addresses and phone numbers. Open rptPhoneBook in preview view. This report prints the data in two snaking (newspaper-style) columns (see Figure 3-7).

Figure 3-7. The two-column rptPhoneBook report
figs/acb2_0307.gif

3.3.3 Discussion

When you create a report, Access assumes you want only one column unless you specify otherwise. If you want more than one column, you must adjust the layout properties of the page using the Columns tab of the Page Setup dialog. The key settings are Number of Columns, Column Spacing (the extra margin between columns), Width (the width of each column), and Column Layout (whether Access first prints an entire column or an entire row). If you want to produce snaking-column (newspaper-style) reports, select "Down, then Across" for Column Layout; for mailing-label-type reports, choose "Across, then Down". For most purposes, you can ignore the other settings.

You will usually create groups in reports that break on the value of a field itself. For example, grouping on Company will trigger a new group for each new unique value of the Company field. Access, however, includes two group properties that allow you to alter the frequency of groupings: GroupOn and GroupInterval. Depending on the data type of the grouping field (see Table 3-6), you can use GroupOn to group on some subset of characters (Text), a range of numbers (Number, Currency), or a period of time (Date/Time). Using the GroupInterval property, you can adjust the grouping further?for example, you could break on the first two characters of a name, every $10, or every two months.

Table 3-6. GroupOn property choices

Data type of field

GroupOn choices

Text

Each Value (default)Prefix Characters

Number, Currency

Each Value (default)Interval

Date/Time

Each Value (default)YearQtrMonthWeekDayHourMinute

When you use the GroupOn property to group on anything other than Each Value, you must realize that the records within the groupings will not be sorted. This means that in most cases you'll also need to include a second sorted copy of the field with GroupOn set to Each Value. This is what we did in the rptPhoneBook example.

Sorting or Grouping?

When you add a field or expression to the Sorting and Grouping window, you may wonder what determines whether a field is a group or merely a sort. No single property determines this?rather, a field becomes a group field if you set either GroupHeader or GroupFooter (or both) to Yes. You can convert an existing group field to a sort field by setting both of these properties to No.


There are several section, report, and group properties that you can adjust to control whether a group is kept together on the same column or page and whether a new column or page is started before or after a group.

3.3.4 See Also

See the Solution in Recipe 3.14 for more details on controlling page and column breaks.