Forms & Reports:
Multiple Columns

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

By packing the Detail data into two or more columns on the page, you can often save a lot of paper.

To create columns of records you must deal with both the Margins and Columns tabs in the Page Setup  dialog, as well as the layout of the report and its Detail section. You will likely need to tweak the  settings more than once to get everything looking good on the page. Frustrating, for sure!

Print Preview: multiple column report

Language Arts Classes: 2 columns
Grades for three subjects at end of the year

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/LinkTo subtopics
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & Reports Arrow: subtopic open
    Icon: StepSwitchboard Form
    Icon: StepCustom Dialog
    Icon: StepLabel Wizard
    Icon: StepChart Wizard
    Icon: StepMultiple Columns
    Icon: StepSubforms
    Icon: StepSubreports
    ExercisesTo subtopics


Report Design View for Multiple Columns

Multiple columns do not show in Report Design view. What you see is not quite what you get!

The same layout in Report Design View, below, can produce both the one-column report and the two-column report! The difference is in the choices in Page Setup.

Report Design View: width sized for 2 columns Print Preview: one column Print Preview: 2 columns

In a four-column version of the same report, below, the Detail section in Report Design View appears to have a LOT of wasted space.  Why are the controls are so tightly packed at the left? The Print Preview shows why! All that empty space got filled up with columns. Again, the choices in the Page Setup dialog make a big difference.

Report Design View: to produce 4 columns Print Preview: 4 columns

Page Setup Dialog: Creating Multiple Columns

Dialog: Page Setup - 2 columns, Down and AcrossStrangely, you configure columns in the Page Setup dialog on the Columns tab, NOT in Report Design View.

Under Grid Settings:

  • Number of Columns: Enter the number your want. (Duh!)
  • Row Spacing: Adds white space between records going down the column. If you already allowed some extra space in the Detail section, this value can be zero.
  • Column Spacing: Adds space between columns. If the Detail section has enough empty space at the right and left already, this value also can be zero.

Under Column Size you have two ways to go to set the size of a record in a column:

  • Print Preview: 2 columns. Report and Page header/footer within column widthSame as Detail checked;
    and Height automatically entered from Detail values.
     Same as Detail - checked. Automatically gets Width & Height from Detail section
    With this choice, the Report Header/Footer and the Page Header/Footer must fit in the first column's width.
    In the example at the right the Page Footer looks odd because it is centered under the first column instead of on the whole page. The report's title fits nicely in a column and looks OK in this report, but it can't be centered on the page either, if you choose Same as Detail

  • Print Preview: 4 columns with report and page header/footer stretching across the pageSame as Detail unchecked;
    Enter your own choices for Width & Height values.
    Same as Detail - No; Enter your own values for Width & Height
    With this choice, the Detail section in Report Design View must have all its controls at the left and within the width and height you entered.
    Report Design View: small Detail layout allows for multiple columns 
    The Report and Page Headers/Footers will show just as the design view shows. They can be centered on the page, for example.

Measure the Spaces:

Print Preview: Dimensions labeledTweaking the measurements is really important to creating a successful multiple column report. The following measurements must work together to fit all the data in columns on the page.

  • Report Design View:
    • Width/Height of Detail section or area containing controls
  • Page Setup Dialog:
    • Margins tab:  Left & Right margins especially. Default is 1" for all margins.
    • Columns tab: Column Spacing. Default is 0.25".
    • Columns tab: Column Size - Set your own Width and Height or Same as Detail

Print Preview: data cut off at rightIf you are not careful, some of your data may be cut off down the right margin. In this example the report's width was too wide for 2 columns to fit on the paper. You may get a warning message about this, but not always!

Column Layout:

You have two choices for how records flow through the columns - across the columns first and then drop down or go down the column first and then back to the top of the next column. It may be hard to see the difference in Print Preview at first glance. Check the sorting. Most reports should probably should use Down and then Across, like newspaper columns.

  • Page Setup Dialog:
    • Columns tab:
      • Down and then Across Column Layout: Down, then Across
      • Across and then Down Across, then Down

Headers and Footers:

All sections of a report have the same width. If you accept the default Page Setup values, it's not just the Detail section that goes into columns. It's the whole report, including Page Header/Footer and Report Header/Footer. This means that things that are centered in Report Design View are centered on the report's width (that is, the column) and not on the whole page. This becomes more obvious with more columns.

Report Design View: to produce 4 columns  Print Preview: 4 columns

In the example above of a 4-column report, the title and page numbers are centered in the report in Report Design View and over the first column in Print Preview. Changing the example report to 4 columns, required moving the grades underneath the name and resizing the report title and divider lines. Looks at weird in Print Preview, don't you think? There is a way around this!

Dialog: Page Setup - for report with 4 columns and full with report headerBetter Method: Design your Report and Page Header/Footers to span the full width of the page. Design the Detail section to fit in 1 column's width. Set the column width specifically in the Page Setup dialog. Do not use Same as Detail. With this method you can put labels in the Page Header that will show above each column on each page. Naturally it may take a lot of tinkering to get them to line up nicely with the data.

 Report Design View: 4 col. report with full width report header Print Preview: 4 column report with full width report header

Icon: Step-by-Step 

Step-by-Step: Create Multiple Columns

 Icon: Step-by-Step

What you will learn:

to use Page Setup to create multiple columns
to design the report to use all of the page
to design the report to have full-width report header with multiple Detail columns

Start with:  Class diskresource files, worldtravel.mdb from the previous lesson

For this exercise you will take an existing report and change it to use multiple columns. Then you will make some adjustments to the formatting and layout so that it looks good in the new arrangement.

Open Existing Single-Column Report

  1. Open the report Clients - country totals from the Database Window.
    You created this 3-page report in the first part of a previous lesson. The clients of World Travel Inc. are grouped and counted by country and postal code.

    Print Preview: Clients - country totals

  2. Report Design ViewInspect the pages.
    Clearly there is a lot of wasted space in this layout. Since the report uses 3 pages, let's try to squeeze the records into 3 columns on one page.
    You will have to reduce the space taken up by the Detail controls.
    The report's title is about the same width as the current width of the Detail controls. You have two choices about that. You could revise and shorten the title but for this lesson you will make the proper choices in Page Setup so that the title can extend across multiple columns.


Report Design View

You need to rearrange the controls in the Detail and group footers to get them into a smaller space.

  1. Open the Page Setup (File | Page Setup) and check the values for the margins.
    Currently the report's left and right margins should be 1", top margin is 1"and bottom margin is 0.5". If they are not, change the margins.
    Calculating: The paper is 8 ½" x 11", so subtracting 2" for the two side margins leaves 6 " for the 3 data columns that you want. Each column could be 2" wide, leaving some extra space in between as Column Spacing. Let's move some controls around and see if we can make that work.

  2. Report Design View: after rearranging controlsSwitch to Report Design View.

  3. Drag the right edge of the report to expand it to 6 " wide.
    Remember, the page margins are not shown in this view.

  4. Move the title in the Report Header to the center of the width.

  5. Move the controls for your name and the page number in the Page Footer to the center of the width.

  6. Move the Detail controls to the left, nearly to the edge of the report.

  7. Move the Total controls in the group footers left, slightly staggered.

  8. Select all 3 text boxes with the expression =Count(*) and set the Width in the Properties dialog to 0.4" wide.

  9. Position the text box for the Country total to fall across the 2" line, as in the illustration.
    (There is a reason!)

  10. Select the two divider lines by dragging across where they should be.
    One divider line is above the label Country in the group header. The other is at the top edge of the Detail section and does not really show.

  11. In the Properties dialog set the width of each line to 2".

Page Setup Dialog

Assuming that your rearranging of the controls on the report is going well, you still have to make changes to the Page Setup setting.

  1. Dialog: Page SetupOpen the Page Setup dialog.

  2. On the Columns tab, set the following:
       Number of Columns = 3
       Width = 2"
       Same as Detail = unchecked
       Down, then Across

  3. Click on OK to close Page Setup.

  4. Print Preview: 2 pagesSwitch to Print Preview.
    This is very close to want we want!

  5. Inspect the pages carefully.

    Whoops! You should see at least two problems, depending on exactly how you positioned the controls:

    • The Total for Country sum is not showing. Since you set the Width of the column to 2" and deliberately positioned the control across the 2" line, of course it got cut off!
      (Good lessons show you how to handle mistakes and problems, so we sometimes have to create them on purpose!)

    • There is just one country on page 2. So close!

    To get more space you can change the margins.

  6. Dialog: Page Setup - MarginsOpen Page Setup again, while still in Print Preview, this time to the Margins tab.

  7. Change the all margins to 0.5".
    The Bottom margin was already at 0.5".

    This will release some space at the top as well as on both sides.

  8. Click on OK.
    The preview changes to use the new settings. Sweet!
    Print Preview: after setting left and right margins
    The columns rearranged themselves to use the new vertical space, but the columns just moved over. They did not automatically use all of the new width.
    There is extra space at the bottom of the third column, yet the report footer, with the count of clients for the whole report, stayed on page 2. A strange trick for Access to play on us.
    What is happening with the Report Footer? The design gave it 6" of width, just like the header. So it won't fit into a column. It's only the Detail section that can wrap in columns when you don't use Same as Detail.

Adjust Column Width

  1. Print Preview: Total for Country now showingWhile still in Print Preview, open Page Setup yet again, to the Columns tab.

  2. Change the column Width to 2.3".

  3. Click on OK.
    The preview refreshes to use the new width.
    Hurrah! The Total for Country value shows now that the column is wider. But the grand total for the report is still off by itself on a separate page.

Move Total

Since that report footer won't fit into a column, you can move the Total of Clients in the report's Header instead.

  1. Switch to Report Design View.

  2. Report Design View: total moved to headerIn the Report Footer select the control for the total and its label.

  3. Drag the selected control and label and drop, centered below the report's title text box.
    The section automatically enlarges vertically.

    Be sure that there is no white space above or below the two labels and none between them either.

  4. Print Preview: with blank pageSwitch to Print Preview.
    Looks good, but wait! The navigation button at the bottom shows that there is more than one page.

  5. Click the right arrow on the navigation bar.
    The next page is blank! Whatever is happening??
    The Report Footer still exists. It just doesn't have any controls in any more. That is easy to fix!!

  6. Switch to Report Design View.

  7. Report Design View: Hide the footerDrag the bottom edge of the Report Footer up to meet the selection bar and there is no more white showing for that section.

  8. Switch to Print Preview.
    Now there the report fits on exactly 1 page. Whew! What a lot of tinkering that took.

    Problem: Still two pages
    Check the spacing in the Report Header section. The section height should be no more than 0.55" when the two labels are touching, with little or no white space above or below them. In the illustration the height is 0.5".

  9. Icon: Save Save the report as Clients - country totals-3col

  10. Icon: Print Print the report. (one page)

Another adjustment you could have made is to reduce the Page Header height to zero, like you did for the Report Footer.