Multi-column sub-report not working correctly

[Burton Roberts]

Using Access 2003

I have a one column subreport that I've set to print multiple columns in the
page setup dialog. When it is NOT embedded in the parent report the
multi-column setup works correctly. But when the subreport is embedded in a
group footer of the main report, it only prints one column per page.

Any help on this?

Thanks
Burton Roberts

 

[Marshall Barton]

Using Access 2003

I have a one column subreport that I've set to print multiple columns in the
page setup dialog. When it is NOT embedded in the parent report the
multi-column setup works correctly. But when the subreport is embedded in a
group footer of the main report, it only prints one column per page.

Since subreports are unaware of page related issues, you can
not use the Down then Across option. Change it to Across
then Down and it should at least do the multiple columns.

Your next question is probably going to be "but I want Down
then Across, how do I make it work?". And the answer is you
can't. With a convoluted query you can calculate two fields
yhat can be used to sort the report in such a way that
Across then Down looks like Down then Across. But the query
is more than a little messy and it only works if your
records already have a unique sort order so it is rarely
worth the effort.

 

[Burton Roberts]

Thanks for the reply. Yes, I would have preferred Down and then Across, but
I think we can survive with Across then Down, if we have to.

Thanks again
Burton Roberts

 

[Duane Hookom]

Assuming you want to print the Products in Northwind in three equal columns
down then across, you would need to use a query like:

SELECT Products.ProductName,
DCount("*","Products", "ProductName <""" & [ProductName] & """") Mod
(DCount("*","Products")\3+1)+DCount("*", "Products", "ProductName <""" &
[ProductName] & """")*0.001 AS AtD2DtA
FROM Products
ORDER BY Products.ProductName;

This would allow you to set the Sorting and Grouping to the AtD2DtA column
and the columns to display across then down. They would look like they are
sorted Down then Across.

--
Duane Hookom
MS Access MVP

Thanks for the reply. Yes, I would have preferred Down and then Across,
but I think we can survive with Across then Down, if we have to.

Thanks again
Burton Roberts

 

[Marshall Barton]

Thank Duane.

I did warn you that it would be messy ;-)

And if there are a lot of records it could be sloooowwwww.

 

[Duane Hookom]

Agreed on the slowness since the function uses 3 domain aggregate functions.
There may be some futzing with some of the numbers if additional columns are
required. This worked for the 77 records in the product table. I didn't try
adding or deleting a couple records and testing the report.
--
Duane Hookom
MS Access MVP

Thank Duane.

I did warn you that it would be messy ;-)

And if there are a lot of records it could be sloooowwwww.
--
Marsh
MVP [MS Access]

Thanks for the reply. Yes, I would have preferred Down and then Across,
but
I think we can survive with Across then Down, if we have to.

 

[Burton Roberts]

That's interesting, but in my situation I know I will have 3 columns, each
with it's own header generated through the sorting/grouping option. I
figure I can just have 3 one-column subreports as long as each column is
short enough to fit on one page, which they are for now, at least.

Thanks again

Agreed on the slowness since the function uses 3 domain aggregate
functions. There may be some futzing with some of the numbers if
additional columns are required. This worked for the 77 records in the
product table. I didn't try adding or deleting a couple records and
testing the report.
--
Duane Hookom
MS Access MVP

Thank Duane.

I did warn you that it would be messy ;-)

And if there are a lot of records it could be sloooowwwww.
--
Marsh
MVP [MS Access]

Thanks for the reply. Yes, I would have preferred Down and then Across,
but
I think we can survive with Across then Down, if we have to.


"Marshall Barton" wrote in message [...]
With a convoluted query you can calculate two fields
yhat can be used to sort the report in such a way that
Across then Down looks like Down then Across. But the query
is more than a little messy and it only works if your
records already have a unique sort order so it is rarely
worth the effort.

 

[Rick Brandt]

That's interesting, but in my situation I know I will have 3 columns,
each with it's own header generated through the sorting/grouping
option. I figure I can just have 3 one-column subreports as long as
each column is short enough to fit on one page, which they are for
now, at least.

Well if the three columns will always fit on one page then you can set CanGrow
of the subreport control to "no", make the control the full height required, and
then Columns of Down, Then Across will work.

It is only when a subreport control has CanGrow = "yes" that Across, Then Down
must be used.

 

[Burton Roberts]

Oh, that's cool, and should work for me!

Thanks

 

[Marshall Barton]

Well if the three columns will always fit on one page then you can set CanGrow
of the subreport control to "no", make the control the full height required, and
then Columns of Down, Then Across will work.

It is only when a subreport control has CanGrow = "yes" that Across, Then Down
must be used.

Good catch Rick.

I should have mentioned that even if it's rare when a
subreport has a fixed number of records.