Columns: Down and Across Problems

[doyle60]

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

 

[Marshall Barton]

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.

 

[doyle60]

I think I want to give this "fudged sort"---to give it a name---a
try. The best way I see to proceed is to build a query that ranks the
records. I need to do that on each new key, which is called QID:

The records in the new Query will have to come out something like
this:

QID PO Rank
0001 1000 1
0001 1001 2
0001 1002 3
0001 1005 4
0001 1009 5
0002 1003 1
0003 2111 1
0003 2115 2
0003 2888 3

I have a piece of paper in my files on how to do ranks. I haven't
done it in a while but I've been successful.

Then, to do in four columns, I could create new fields in the query
that do this:

Column4: IIf((Rank+0)/4=(Rank+0)\4,4,0)
Column3: IIf((Rank+1)/4=(Rank+0)\4,3,0)
Column2: IIf((Rank+2)/4=(Rank+0)\4,2,0)
Column1: IIf((Rank+3)/4=(Rank+0)\4,1,0)
Column: Column4+Column3+Column2+Column1

Sorty by: Column and Rank.

That would do it, right? This method uses the skills I know and if
there is something easier but not too complicated, I would love to
hear it.

Matt

 

[Marshall Barton]

I think I want to give this "fudged sort"---to give it a name---a
try. The best way I see to proceed is to build a query that ranks the
records. I need to do that on each new key, which is called QID:

The records in the new Query will have to come out something like
this:

QID PO Rank
0001 1000 1
0001 1001 2
0001 1002 3
0001 1005 4
0001 1009 5
0002 1003 1
0003 2111 1
0003 2115 2
0003 2888 3

I have a piece of paper in my files on how to do ranks. I haven't
done it in a while but I've been successful.

Then, to do in four columns, I could create new fields in the query
that do this:

Column4: IIf((Rank+0)/4=(Rank+0)\4,4,0)
Column3: IIf((Rank+1)/4=(Rank+0)\4,3,0)
Column2: IIf((Rank+2)/4=(Rank+0)\4,2,0)
Column1: IIf((Rank+3)/4=(Rank+0)\4,1,0)
Column: Column4+Column3+Column2+Column1

Sorty by: Column and Rank.

That would do it, right? This method uses the skills I know and if
there is something easier but not too complicated, I would love to
hear it.

That might do it?? But its easier to use the Mod operator.

Column: Rank Mod 4

Unfortunately, that is nowhere near sufficient if the
subreport is split across a page boundary. In this case,
you would have to know how many records will fit on a page
and add another sort field:

Page: Rank Mod NumPerPage

Obviously(?) this will only work if you can guarantee that
every page (except the last) displays the same number of
subreport records.

 

[doyle60]

Actually, my method does not work at all. I don't know what I was
thinking. What is this mod mod operator? Where can I read about it?
I have Access 97, by the way. I'm not sure how to employ it.

Matt

 

[doyle60]

I tried to find out which row each data point belongs to using code.
This is possible using the remainders after division. But the problem
is that the two shapes are not always equal. If I have 14 records,
they lay out like this:

01---02---03---04
05---06---07---08
09---10---11---12
13---14

But my row analysis assumes this shape:

01---05---09---13
02---06---10---14
03---07---11
04---08---12

You see the difference?

Since I can't trick it to use this shape, I would than have to trick
my code with three different shape endings. This is too much and just
not worth it.

Unless you have a elegant solution, I think we should end it here.

Hey! I just thought of something---maybe I could divide the results
into four different subs?

Matt

 

[Marshall Barton]

Actually, my method does not work at all. I don't know what I was
thinking. What is this mod mod operator? Where can I read about it?
I have Access 97, by the way. I'm not sure how to employ it.

In A97 Help, search the Index for MOD

Essentially, N Mod M returns the remainder of N \ M

 

[Marshall Barton]

I tried to find out which row each data point belongs to using code.
This is possible using the remainders after division. But the problem
is that the two shapes are not always equal. If I have 14 records,
they lay out like this:

01---02---03---04
05---06---07---08
09---10---11---12
13---14

But my row analysis assumes this shape:

01---05---09---13
02---06---10---14
03---07---11
04---08---12

You see the difference?

Since I can't trick it to use this shape, I would than have to trick
my code with three different shape endings. This is too much and just
not worth it.

Unless you have a elegant solution, I think we should end it here.

The general formula is along these lines:

(<rank> * <number of columns>) \ <number of records>

But, I don't think you will like that if the subreport does
not fit on a single page,

In general, I don't think there is an "elegant" solution so
I agree you should live with using Across then Down.

 

[doyle60]

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

 

[Marshall Barton]

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.

 

[doyle60]

< 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

 

[Marshall Barton]

< 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.

 

[doyle60]

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