When I choose Across and Down for data on a subreport, the subform
prints out correctly on the main report. It makes it four columns as
I wanted.

But I really want to use Down and Across (not Across and Down). But
when I use that option, the main report does not columnate it at all,
it just prints it out as one column.

This is annoying. What am I doing wrong?

When I preview the subreport (not the main report), it does columnate
it correctly with the Down and Across option. But when I go to the
main report and do a preview, it makes it the one column.

It happens whether I choose two or three columns and no matter how
wide the control on the main form is for the sub report.

I don't get it.

Any ideas?

Simply changing from Down and Across to Across and Down shouldn't make
you change anything, as far as I know.

Thanks,

Matt

(E-Mail Removed) wrote:

>When I choose Across and Down for data on a subreport, the subform
>prints out correctly on the main report. It makes it four columns as
>I wanted.
>
>But I really want to use Down and Across (not Across and Down). But
>when I use that option, the main report does not columnate it at all,
>it just prints it out as one column.
>
>This is annoying. What am I doing wrong?
>
>When I preview the subreport (not the main report), it does columnate
>it correctly with the Down and Across option. But when I go to the
>main report and do a preview, it makes it the one column.
>
>It happens whether I choose two or three columns and no matter how
>wide the control on the main form is for the sub report.
>
>I don't get it.
>
>Simply changing from Down and Across to Across and Down shouldn't make
>you change anything, as far as I know.


The problem is that the main report is "in charge" of page
related actions so the subreport has no idea when it should
move to the next column. Similarly, a subreport's Page
Header/Footer sections are not used and its Page event will
not be triggered. Regardless of how much sense it makes, it
has always been this way.

Your only other options are to set the subreport control's
Height at design time and set its CanGrow property to No.
Obviously this idea is useless if you can't tell a priori
approximately how many records will be in the subreport.

Or, you can try to fudge the subreport's record source
records so that they are sorted in such a way that Across
then Down looks as if it were down then across. This won't
really do what I think you want if the subreport is split
across multiple pages.

--
Marsh
MVP [MS Access]

Okay, I solved this problem. Just for the record I will put here how
I did it. It is probably not the easier way but it works.

First, this method uses as many sub reports as you want columns. So
if you want four columns, as in this example, you are going to create
four subs. This method is also nice because the Hide Duplicates
action will still work nicely, repeating the info at the top of each
column too.

So take these steps:

1) Build a query that ranks your data in the order you want it. (I
cheated and built a query that sorts the data as I want it and pastes
it that way into a table with an autonumber. I then built a query
that subtracts the low autonumber from each section (and added 1) to
get a 1, 2, 3, etc. ranking for each time the data is pasted into the
table. Each time you run the report, before opening, a delete query
and an append query must run.)

2) Build a query that returns the number of records that each sub
should have.

With CountOfBNPO as the number of records in all the four subs (or put
another way, the number of records for each key), they should look
something like this.

Column1Count: IIf([CountOfBNPO]/4=[CountOfBNPO]\4,[CountOfBNPO]/4,
[CountOfBNPO]\4+1)

Column2Count: IIf([CountOfBNPO]/4=[CountOfBNPO]\4,[CountOfBNPO]/4,
[CountOfBNPO]\4+1)-IIf([CountOfBNPO]/4-[CountOfBNPO]\4=0.25,1,0)

Column3Count: IIf([CountOfBNPO]/4=[CountOfBNPO]\4,[CountOfBNPO]/4,
[CountOfBNPO]\4+1)-IIf([CountOfBNPO]/4-[CountOfBNPO]\4=0.5 Or
([CountOfBNPO]/4-[CountOfBNPO]\4=0.25),1,0)

Column4Count: [CountOfBNPO]-[Column1Count]-[Column2Count]-
[Column3Count]

The last is a bit of a short cut but it guarantees something won't be
missed.

Someone could probably shorten these but it works.

3) In the same query, create fields to figure out which column the
records should fall into:

Col1: IIf([Rank]<=[Column1Count],1,0)

Col2: IIf([Rank]>[Column1Count] And ([Rank]<=[Column2Count]+
[Column1Count]),2,0)

Col3: IIf([Rank]>[Column1Count]+[Column2Count] And
([Rank]<=[Column2Count]+[Column1Count]+[Column3Count]),3,0)

Col4: IIf([Col1]+[Col2]+[Col3]=0,4,0)

Col: [Col1]+[Col2]+[Col3]+[Col4]

So the last here, Col, returns a number 1 through 4 for each column.

4) Create four queries that filters for each column.

Is there an easier way? Probably. But I don't do VBA and this works
for me.

Page breaks are a problem but my data will never be long enough for
that.

Matt

(E-Mail Removed) wrote:

>Okay, I solved this problem. Just for the record I will put here how
>I did it. It is probably not the easier way but it works.
>
>First, this method uses as many sub reports as you want columns. So
>if you want four columns, as in this example, you are going to create
>four subs. This method is also nice because the Hide Duplicates
>action will still work nicely, repeating the info at the top of each
>column too.
>
>So take these steps:
>
>1) Build a query that ranks your data in the order you want it. (I
>cheated and built a query that sorts the data as I want it and pastes
>it that way into a table with an autonumber. I then built a query
>that subtracts the low autonumber from each section (and added 1) to
>get a 1, 2, 3, etc. ranking for each time the data is pasted into the
>table. Each time you run the report, before opening, a delete query
>and an append query must run.)
>
>2) Build a query that returns the number of records that each sub
>should have.
>
>With CountOfBNPO as the number of records in all the four subs (or put
>another way, the number of records for each key), they should look
>something like this.
>
>Column1Count: IIf([CountOfBNPO]/4=[CountOfBNPO]\4,[CountOfBNPO]/4,
>[CountOfBNPO]\4+1)
>
>Column2Count: IIf([CountOfBNPO]/4=[CountOfBNPO]\4,[CountOfBNPO]/4,
>[CountOfBNPO]\4+1)-IIf([CountOfBNPO]/4-[CountOfBNPO]\4=0.25,1,0)
>
>Column3Count: IIf([CountOfBNPO]/4=[CountOfBNPO]\4,[CountOfBNPO]/4,
>[CountOfBNPO]\4+1)-IIf([CountOfBNPO]/4-[CountOfBNPO]\4=0.5 Or
>([CountOfBNPO]/4-[CountOfBNPO]\4=0.25),1,0)
>
>Column4Count: [CountOfBNPO]-[Column1Count]-[Column2Count]-
>[Column3Count]
>
>The last is a bit of a short cut but it guarantees something won't be
>missed.
>
>Someone could probably shorten these but it works.
>
>3) In the same query, create fields to figure out which column the
>records should fall into:
>
>Col1: IIf([Rank]<=[Column1Count],1,0)
>
>Col2: IIf([Rank]>[Column1Count] And ([Rank]<=[Column2Count]+
>[Column1Count]),2,0)
>
>Col3: IIf([Rank]>[Column1Count]+[Column2Count] And
>([Rank]<=[Column2Count]+[Column1Count]+[Column3Count]),3,0)
>
>Col4: IIf([Col1]+[Col2]+[Col3]=0,4,0)
>
>Col: [Col1]+[Col2]+[Col3]+[Col4]
>
>So the last here, Col, returns a number 1 through 4 for each column.
>
>4) Create four queries that filters for each column.
>
>Is there an easier way? Probably. But I don't do VBA and this works
>for me.
>
>Page breaks are a problem but my data will never be long enough for
>that.


You can calculate the column number (0 through3) by using :

Col: 4 * Rank \ CountOfBNPO

This way you can use one query and one report object for the
four subreports. The trick here is to use the same report
in all four subreport controls. You get them to display the
columns separately by adding four hidden text boxes (named
col1, col2,...) and set their expression to =0, =1, =2 and
=3

Then set the first subreport control's LinkMasterFields
property to col1, the second to col2, etc. Set all four
subreport control's LinkChildFields property to the Col
field.

--
Marsh
MVP [MS Access]

< Col: 4 * Rank \ CountOfBNPO >

This simplifation of my process does not work. It places the data
haphazardly in each column. I spent much time working out my process,
and though I imagine there is some more elegant way to do it, this is
not it. I put your formula beside mine and they do not match.

I tried tweeking it a bit but didn't come up with anything. It will
return a fifth column when there are 12 records and the rank is 12.
It fails when the rank is a factor of 4 but fails at other times as
well. For example, when there are 14 records, yours does the last
column below mine does the middle:

Col Col
01-----1-----0
02-----1-----0
03-----1-----0
04-----1-----1
05-----2-----1
06-----2-----1
07-----2-----2
08-----2-----2
09-----3-----2
10-----3-----2
11-----3-----3
12-----4-----3
13-----4-----3
14-----4-----4

Yes, you are undoubtedly correct that you don't have to do all the
subs and different queries for each as I mentioned.

Matt

(E-Mail Removed) wrote:

>< Col: 4 * Rank \ CountOfBNPO >
>
>This simplifation of my process does not work. It places the data
>haphazardly in each column. I spent much time working out my process,
>and though I imagine there is some more elegant way to do it, this is
>not it. I put your formula beside mine and they do not match.


Sorry, I was thinking of something other than what you are
trying to do. Even then my expression was missing some
other factors.

Rather than confuse you any further and save me a headache,
I think I should quit when I'm this far behind. Just go
with your working solution.

Regardless of all that, if you can get all the Col
calculations into a single query, you would then only need
one report object for all four subreport controls. But, you
may not care about that at this point.

--
Marsh
MVP [MS Access]

Thanks. I just created code for drawing lines around the four subs,
making them all equal to the longest, the first. So now it looks just
great.

All this was a completely tedious project but I just had to challenge
myself.

Thanks again. Without the confirmation you gave me that there wasn't
an easy solution, I would have never attempted all this.

Matt