Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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

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

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

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

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

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

Figure 3-6. The completed rptPhoneBook report in design view

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

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 furtherfor
example, you could break on the first two characters of a name, every
$10, or every two months.

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.

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.