Visit Applied Office's Website Contact  

March 2006 : access


  using multi-column reports

Reports are one of the best ways to view data. You can dress up reports by adding formatting and pictures, but you can also change the layout of the report. One way to change the report layout is to use columns in the report. For example, you could create a two-column telephone-style report that lists the names and numbers of your customers. We'll use this example to show you how to use columns in a report in this article.

Creating the report

The first step is to create a report. The easiest way to do this is to use the Report Wizard. In the Database window, double-click the "Create report by using wizard" option. The first screen of the wizard appears. Click the "Tables/Queries" list arrow to choose the database table or query that contains the data you want in the report.

Once the table or query is selected, its fields are shown in the list. To add a field to the report, click the field name and click the > button. Once all the fields you want to include in the report have been added, click Next to move on to the next screen.

After the fields are chosen, define the grouping you want to use. For example, in a report for contacts, you may want to group them by the first letter of their last name, so that all the A's appear in a group, and so on. To do this, click the LastName field and click the > button. The LastName field appears as the grouping header in the preview of the report.

To group by the first letter in the LastName field, click the Grouping Options button. The Grouping Intervals dialog box appears. Click the "Grouping intervals" list arrow and select 1st Letter. Click OK. The preview of the report is now updated to group by the first letter of the LastName field. Click Next to continue with the report wizard.

The last thing you need to do in the Report Wizard is specify how the report will be sorted. The records will be grouped by first letter, but they won't appear in order. Sort first by the LastName field so records are sorted like this: Amundson, Anderson, and Aziz. Then sort by FirstName so records will be sorted like this: Erick Anderson, Matilda Anderson, and Paul Anderson.

Click Finish, or click Next to define other report options.

Adding columns

Once the report is previewed and contains the fields, grouping and sorting you want, you can add columnar page layout. Make sure the report you want to change is either selected in the Database window, or open in Print Preview. Page settings will only apply to the selected report.

First, open the Page Setup dialog box: Select File > Page Setup and click the Columns tab. Type the number of columns you want to use in the Number of Columns text box.

Now for the most complicated part of the process: figuring out the column size needed for a multi-column page layout. Unfortunately, Access doesn't automatically figure this out, but we've mapped out an example here.

There are a number of areas to account for: the columns themselves, column spacing (space between columns) and page margins.

Here's the formula:

8.5" (page width) - 2" (right and left page margins) - 0.25" (column spacing)
/ 2 (the number of columns you want)
= 3.125" (column width)

Once you've figured this out, enter the number in the Column Width text box.

The last option you need to specify in the dialog box is how you want the data to be displayed in the columns. Click the "Down, then Across" option so the data is organized by column up and down. Click OK.

Once the page setup and report info is put together, you can fix up the appearance of the report. First, preview the report.

There are a few things you will probably want to change:

Page Header: We added Name and Phone labels above both columns.

Detail section: We moved the FirstName and Phone fields below the LastName. The size of the field boxes was also changed.

See the examples below to compare the default report design to a design that is tweaked for a two-column layout, and a preview of what the new design looks like when printed.


  quick jump

Jump to other stories through these links:

[ contents.htm]

  keyboard shortcut of the month

To quickly insert today's date in any form or data field, just hit Ctrl+; (that's Control-Semicolon).

And for the time, it's Ctrl+Shift+;.

In fact, this shortcut works in Excel too.

  screen shots

The following screen shots illustrate the articles on the left.  Click on one for a larger view.