formatting Access report

[TiderInsider]

Here is my delima: I am working on a function that will create a cross tab query and then a report based on that query. The only problem is the formatting of the report.

I want all the info to fit on one page and the number of columns will change from query to query. I also want to skip a line at a couple of times during the report.

My query:

Duration     0     5     10     15     Total

Quarter1     X     X      X       X        X
Quarter2     X     X      X       X        X
Quarter3     X     X      X       X        X
Quarter4     X     X      X       X        X
Year1        X     X      X       X        X
Quarter5     X     X      X       X        X
Quarter6     X     X      X       X        X
Quarter7     X     X      X       X        X
Quarter8     X     X      X       X        X
Year2        X     X      X       X        X
Year3        X     X      X       X        X
Year4        X     X      X       X        X
Year5        X     X      X       X        X

What I would like my report to look like

Duration     0     5     10     15     Total

Quarter1     X     X      X       X        X
Quarter2     X     X      X       X        X
Quarter3     X     X      X       X        X
Quarter4     X     X      X       X        X

Year1        X     X      X       X        X

Quarter5     X     X      X       X        X
Quarter6     X     X      X       X        X
Quarter7     X     X      X       X        X
Quarter8     X     X      X       X        X

Year2        X     X      X       X        X

Year3        X     X      X       X        X
Year4        X     X      X       X        X
Year5        X     X      X       X        X

Is this possible? What is the best approach to take to achieve this kind of formatting.

Thanks in advance for any help.

 

[SydneyGeek]

Assuming you can get the basic layout in your report, you can play with the line spacing in the report code.
Report Design view, right-click Detail and select the Format event.
Double-click so you see [Event Procedure], then click the builder (...) at the right end of the line.
Now you're in the code area. Your cursor should be between
Sub Detail_Format and End Sub.
Now I'm guessing a bit -- you will need to dig a bit in the properties -- but you'll need something like this in the end:

Sub Detail_Format
  If Left([Duration],4)="Year" Then
    'write some code that doubles the line spacing
  End If
End Sub

HTH
Denis

 

[blueshark]

I think the code solution is quickest to accomplish exactly what you said.

but...

I'd be interested to see the source data table.

If I were correct in assuming year1,2.... is an aggregate of the respective quarters, you could use totals in the report by grouping on the year assuming that the year can be assigned to each row somehow.

 

[TiderInsider]

Year 1 and 2 are geometric average of the ratios listed in the quarters, so I could calculate them, but I am pulling these number straight from a mainframe .prn file, so my task is to manipulate the data given to me - not to calculate.

 

[blueshark]

I see. then the code example is perfect for this.