by Sal Ricciardi
Most reports consist of a single column that spans the width of the page, but sometimes that won't do. Suppose, for example, that you want to print a report that employs several newspaper style columns. In this article, learn how to create a multiple-column report that "snakes" up and down the page. We provide a sample database you can download.
|Microsoft Office Access 2003
Microsoft Access 2000 and 2002
Question: How do I create a report that uses multiple "snaking" columns?
You create your basic report as you normally would. Then, with your report selected, change the settings in the Columns tab of the Page Setup dialog box. The page setup settings are saved with your report. You also need to carefully position the controls on your report to support the multiple-column layout.
The steps you need to take are as follows:
- Create the basic report by using the Report Wizard
- Use the Page Setup dialog box to change the column settings
- Fine-tune the settings and the report design
The sections that follow cover these steps.
Create the basic report by using the Report Wizard
Suppose you want to create a contacts report with a phone book-style look to it, so that several columns appear on each page. In this report, you want records to appear adjacent to one another, both vertically and horizontally (like the previous illustration).
To get started, use the Report Wizard to create a new report, and select the table that contains your contact names. Next, choose the fields you want to include in the report, such as Lastname, Firstname and Telephone. After that, the Report Wizard lets you choose any grouping levels. Choose a grouping level when you want to group similar records together —you can use a Group header or footer, or both. For this example, let's assume that you want to group by the first letter of the last name, so that names starting with "A" are shown in one group, names with "B" in the next group, and so on. Select the LastName field, click Grouping Options, and in the Grouping Intervals dialog box, set the Grouping intervals property for the LastName field to 1st Letter. Click Next to continue to the next page of the wizard.
When the Report Wizard allows you to choose a sort order for detail records, click LastName in the first box, FirstName in the second box, and Telephone in the third box. Doing this ensures that the names will appear sorted within each group. Click Finish for the Report Wizard to generate the report.
Step-by-step instructions for creating the basic report by using the Report Wizard:
To start the Report Wizard
- In the Database window, under Objects, click Reports.
- In the Database window, click New on the toolbar.
- In the New Report dialog box, click Report Wizard
- Choose a table or query to use for the report, such as the Names table, and then click OK.
To create the report and choose a grouping level
- In the list of Available Fields, double-click each field to include in your report. For example, choose LastName, FirstName, and Telephone.
Access moves each field you choose to the Selected Fields list.
- Click Next.
- Choose a grouping level. To choose LastName for the grouping level, double-click LastName, or click LastName and then click the Add button ( > ).
Access shows LastName as the grouping level.
- Click Grouping Options
- Click 1st Letter in the Grouping intervals box, and then click OK.
Access now shows LastName by 1st Letter as the grouping level.
- Click Next to continue to the next page of the Report Wizard.
To set the sort order for the report
- Click the first drop-down list, and then click the first field to sort by. For example, click LastName.
- Click the second drop-down list, and then click the next field to sort by. For example, click FirstName.
- Click the third drop-down list, and then click the next field to sort by. For example, click Telephone.
- If you want, click the fourth drop-down list, and then click the next field to sort by.
- Click Next.
To finish the Report Wizard
- Complete the next page of the wizard to specify layout and orientation, and then click Next.
- Complete the next page to choose a report style, and then click Next.
- Type a title for your report, and then click Finish.
The Report Wizard generates the report.
Use the Page Setup dialog box to change the column settings
When you preview the report after the Report Wizard finishes, you'll see that Access produced a single column report, which is the default. To change the report so that it uses multiple columns, click Page Setup on the File menu, click the Columns tab, and type the number of columns you want in the Number of Columns box. Make sure your report is selected in the Database window (or displayed in the Print Preview window) before you open the Page Setup dialog box. The changes you make to the settings in the Page Setup dialog box apply only to the currently selected report, and not to other existing reports or reports you may create later.
Column settings you can change in the three sections of the Page Setup dialog box:
Grid Settings section
- Number of columns Enter the number of columns per page.
- Row Spacing If additional space is desired between each detail record, enter the amount of space here.
- Column Spacing Enter the amount of space you want between columns.
Column Size section
- Width Enter the width of each column.
Keep in mind the margin settings and column spacing when setting this value.
- Height Enter the height of one row in the column.
- Same as Detail Check this to set the column width and height equal to the width and height of the Detail section.
Column Layout section
- Down, then Across Check this when you want records to print down the first column, then down the second column, and so on. Columns run across the page.
Choose this layout for a snaking-column, or newspaper-style column effect.
- Across, then Down Check this when you want records to print across the first row, then across the second row, and so on. Rows run from the top of the page to the bottom.
Fine tune the settings and report design
Once you have the design of the basic report completed and have set the column settings in the Page Setup dialog box, you will likely need to experiment to arrive at an optimal design. It's worthwhile to keep in mind that a page in a multiple column report consists of a number of space-occupying areas. For example, there are the columns themselves, the space between the columns, and the space occupied by the page margins. So if your report is 8.5 inches wide and you use a 0.75 inch margin on the left and on the right, you're starting with 7 inches of available space (8.5 minus 1.5). If you design your report for two columns and use 0.25 inches for the space between columns (the column spacing setting in the Page Setup dialog box). you now have only 6.75 inches available for the columns themselves. Thus, the width of the information in your Group and Detail sections must not be greater than 3.375 inches (6.75 / 2 columns) in order to fit properly.
I used those settings to create the two-column example report included in the sample database.
Note that the Group header and footer sections and the Detail section of a multiple-column report span the width of one column. The Report header and footer and Page header and footer, on the other hand, span the full width of the report.
If the settings in the Page Setup dialog box and/or the width of the controls in the report design result in some information not fitting on the page when printed or previewed, Access displays the "Some data may not be displayed" message. This is a sure sign that you need to fine-tune your page settings and perhaps adjust the size of the controls in your report design. If you see this message, try any of the following:
- Reduce the width of the controls in the Group Header and Footer sections and the Detail section so that they fit within the calculated space available for a single column. See the example earlier in this article to learn how to calculate the space available.
- Reduce the size of the page margins in the Page Setup dialog box. This will increase the space available for the information in your columns.
- Consider choosing a Landscape page orientation instead of Portrait.
- Reduce the number of columns or the width of the columns.
Download the sample database
The sample database includes a two-column, three-column and four-column example of the Contact report shown earlier in this article, using data drawn from the Northwind.mdb sample database that is included with Access.
For more information
- For a well-written book focused on forms, reports and queries, see McFedries, Paul. Microsoft Access 2003 Forms, Reports and Queries. Que Publishing, 2004.
- For an excellent, technical treatment of many topics concerning queries and reports, see Getz, Ken; Litwin, Paul; Gunderloy, Mike. Access 2002 Desktop Developer's Handbook. SYBEX, Inc., 2001.