databasedev.co.uk - database solutions and downloads for microsoft access

Creating a Multiple-Column Report in Microsoft Access:

There may be the time where you need to arrange data into multiple columns in your Microsoft Access reports. There will have been times where you have needed to deal with the issues of trying to squeeze all of the fields for an Access database report into the standard width that the database report allows.

It is also very possible that you may have had the opposite to this problem; having just a couple of fields required for the report, and as a result the report will show way too much white space.

This article describes how you can combat this issue, by arranging your data fields into multiple columns on the report page.

Some of your Microsoft Access reports may at times involve long lists of data where just a few fields are needed to be displayed; common occurrences of this may be with product listings or phone number listings. These are best arranged by using multiple columns that allow improved readability and prevent the need to use so many pages of paper in your report.

How to Arrange Data in Columns on a Microsoft Access Report

• Create a new Microsoft Access report in design view
• In the Detail section of the report, place the controls that you want to print within your chosen width
• Add a group header and/or group footer
• Choose File » Page Setup and select the Columns page
• Enter the number of columns and column spacing that you would like to appear in the report
• Choose a column width for your columns
• Select the column layout - either Down, then Across or Across, then Down
• Save the report and preview.

Printing Data in Columns on a Microsoft Access Report

Imagine that you need to create an internal phone list for the company's HR Dept. The layout would need to be easily readable, and include some type of divider for easy reference and you would also like this to appear on a single page report.

You would initially create a Microsoft Access query that will extract the Employee Last Name, Employee First Name and their Office phone number extension. You will also place the records in alphabetical order based on the Employee Last Name, then Employee First Name.

The select query design may look similar to:

Now when we create a new Microsoft Access report, in design view, we use the above query as the source for the report.

In the detail section of the new report, to the left hand side, we can add a text box control, delete the associated label and set the control source of this new text box to:

=[EmployeeLastName] & ", " & [EmployeeFirstName]

This will display the names in the report in the format of "Last Name, comma, space, First Name"

To the right of the Employee Name text box, we add the OfficeExt field.

To ensure that the report is easily readable we will also include a Group Header, that will use an expression to extract the first letter of each Last Name.

In the Sorting and Grouping dialog box, we can enter the following expression in the first Field/Expression row:

=Left([EmployeeLastName],1)

We have set the Group Header property to Yes, and also selected to Keep Together the Whole Group.

Now in the report design, in the new Group Header, we add a text box that uses the same expression as above. This will display the First Letter of the Employee Last Name for each group.

The completed report design should now look like the following:

Now to ensure that the report returns the data in multiple columns, we need to set the options in the Page Setup dialog.

If we go to File » Page Setup and set the following options:

The finished report should look similar to the following: