2 column report but last page only 1 column

[WheelerLake]

My sample report has 185 records in a two column report with 2 subreports (single column) in the report footer. My columns are set to: down, then across. On page 3 of the report the last 30 records are in only 1 column on the left with empty space to the right.

How to I get the remaining records on the last page to make 2 columns of 15 records and no blank space on the right of the page?

My report requirements are for down, then across.

Please see sample report attached.

Please Help, Bob

 

[dhookom]

You can only get equal length columns when using across then down. I have created a calculated column in the report's record source to use for sorting so that I can use across then down.

Duane
Hook'D on Access
MS Access MVP

 

[WheelerLake]

Thanks for your comments Duane. My requirements for the report are down then across. I have played with settings in report properties with no luck and your comments confirm I can't get there using properties. I have tried two subreports representing the two columns. It somewhat worked, but it was difficult to make it behave for different data. I am now pursuing writing what I need in VBA. I'll need to mimic what access grouping does. I have at least proven this idea is possible, but column manipulation and pagination is a bear.

My data looks like this:
RefNo, ID, DescHeading, DescDetail, Status
.
.
.
.


My experience with reports is "get the table/query to look as close to the finished report as possible"
I have not come up with any tricks to transform the data to this:

Heading1 Heading3
ref, descdetail_1, status ref, descdetail_1, status
ref, descdetail_2, status ref, descdetail_2, status
ref, descdetail_3, status ref, descdetail_3, status
ref, descdetail_4, status ref, descdetail_5, status
Heading2 ref, descdetail_6, status
ref, descdetail_1, status ref, descdetail_7, status
ref, descdetail_2, status ref, descdetail_8, status
ref, descdetail_3, status Heading 4
ref, descdetail_4, status ref, descdetail_1, status
ref, descdetail_2, status

Any ideas?

Bob

 

[dhookom]

Sure I have an idea but it hasn't changed from my first reply. You can create a calculated column in the report's record source that can be used to order the records in the report so they appear to be down then across when they are actually across then down. This typically means some work using DCount() based on the field you want to appear as sorting the report.

Duane
Hook'D on Access
MS Access MVP

 

[WheelerLake]

Duane,
I think I understand. You some how sort the records so that an "across then down" column report looks like a "down then across". Please supply additional details. I know how to use dcount in a query to sequence (count) the records, but I can't get my head around how that helps.

Thanks, Bob

 

[dhookom]

I typically start by pasting all the records into Excel and trying to figure out the appropriate order.

Duane
Hook'D on Access
MS Access MVP

 

[WheelerLake]

The report ended up with 7 subreports and logic using temp tables to get the 2 columns in the detail section. 2 temp tables (1 for each column) linked on record count to look like 1 row. I will clean up the code and post in near future along with lessons learned.

Thanks, Bob