Need assistance with Subreport multiple columns

[Therese G. Maddox]

Hi All!

I'm hitting a wall and hoping someone will have a direction to point me in.

I've created a database that has a report with a subreport within. The
subreport has a many to one to the report. If I pull up the report in Form
view, and the subreport is in continuous forms format, I get this:

Frank Smith has 4 orders linked to his employee name. #106, 107, 108v and
109.

Employee: <Frank Smith> (on main report with other info ect)

sub report

#106
Date
New
Company

#107
Date
New
Company

#108
Date
New
Company

#109
Date
New
Company

Etc.

However, I want it to be this way:

Employee <Frank Smith> (main report)

Sub report

#106 #107 #108 #109 Etc.
Date Date Date Date
New New New New
Company Company Company Company

Hence, the continuous form goes ACROSS, not down. This will allow me to add
additional fields BELOW the Date, Name Company fields for my subreport.

I’ve tried everything under the sun listed under multiple columns reports
and nothing works. Any insights??

Thanks,

TG Maddox

 

[KC-Mass]

Hi Therese,

Look at CrossTab queries as the source for your subreport then redesign the
subreport.

Should give you what you want.

Regards

Kevin

 

[Therese G. Maddox]

Thanks Kevin,

Doesn't the CrossTab Query have a limit to how many columns and rows? For
example, in the end, my report will have 27 fields...

#106
Date
New
Company
Field 5
Field 6
Field 7
etc to field 27


#107
Date
New
Company
Field 5
Field 6
Field 7
etc to field 27


#108
Date
New
Company
Field 5
Field 6
Field 7
etc to field 27


#109
Date
New
Company
Field 5
Field 6
Field 7
etc to field 27





#106 #107 #108 #109 Etc.
Date Date Date Date
New New New New
Company Company Company Company
Field 5 Field 5 Field 5 Field 5
Field 6 Field 6 Field 6 Field 6
ect to field 27

Just curious. Trying it now.

 

[Duane Hookom]

I would think that a subreport that has multiple columns displaying across
then down should work for you. Your desired display is not much different
than a mailing label report used as the subreport. Make sure the main report
doesn't contain the detail information.

 

[David]

Hi Therese,

Look at CrossTab queries as the source for your subreport then redesign the
subreport.

Should give you what you want.

Regards

Kevin

 

[Therese G. Maddox]

Thanks, this worked for me, with one exception: I got the report to work but
when I embed the "sub report" into another report, it doesn't show as a multi
column report.

Several things to note:

I am aware that the multi columns don't show except in print preview. The
default view on the report (the subreport in this case) is print preview)

There is no link (child-master) to the main form as it is not needed in my
design. The main form is just for logos, and in my case the labels that are
needed on the left hand side and top because I can't get them on my sub
report.

Is there a setting I'm missing? When I do a print preview of the main form
with the sub form, it just shows the sub form as it looks in form view, which
is wrong.

Thanks.

Therese

 

[Duane Hookom]

Is your use of "form" and "sub form" just a typo? Does the subreport on your
main report stretch horizontally across the entire main report width other
than some labels on the left?

 

[Therese G. Maddox]

Sorry, form meant to be report. My brain is a bit fried.

My multi column report that I want to be a sub report goes horizontally
across the page like this:

field one field two field three field four field five and so on
text 1 text 1 text 1 text 1 text 2
text 2 text 2 text 2 text 2 text 2

No labels on left, as for some reason I can't get it to work on my data.
Tried the KB article but it may be the way my data is set up.

in report view it looks like this:

field one
text 1
text 2
text 3
an so on to text 26

field 2
text 1
text 2
text 3
and so on

field 3
text 1
text 2
text 3 etc.

I hope this answers the question.

Therese

 

[Duane Hookom]

Having field values displayed across the page is not what I would consider a
multi-column report. Based on what you presented as what you "want", looks to
have records displayed one underneath the other rather than displaying
records across.

I'm confused.

 

[Therese G. Maddox]

I'm sure my newness to all of this is confusing everyone.

The "field1" etc I'm referring to is in the header grouping section of the
report. the text fields are in the detail section sorted by their id.
Specifically, there is a one to many relationship between the "field" field
to the text fields"

All of the text fields belong to the field fields. When I create the
report, I just removed all of the extra stuff in the report and moved
everything to the left side, and it made the report view layout:

field1
text 1 (of field 1)
text 2 (of field 1)
text 3 (of field 1)
text 3 (of field 1)
....
field2
text 1 (of field 2)
text 2 (of field 2)
text 3 (of field 3)
....
field3
text 1 (of field 3)
text 2 (of field 3)
text 3 (of field 3)
....

then I made it a multiple column report and played with it until it all
lined up like this:

field 1 field 2 field 3
text 1 (of field1) text 1 (of field 2) text 1 (of field 3) ...
text 2 (of field2) text 2 (of field 2) text 2 (of field 3)....
text 3 (of field3) text 3 (of field 3) text 3 (of field 3).....


On and on for 15 columns....26 rows.

My issue is that I need this to be a subreport. But when I make it a
subreport, it doesn't display the multicolumns. The multicolomns only
display in the subreport when viewing it as a main report on its own in print
preview. I'm aware this is how you have to view it, as viewing it in report
view doesn't show you the multicolumns. I want to have the report be a
subreport and show the mutlicolumns within another report in print preview
and its not doing it.

If this doesn't make sense, I just need a fresh brain.

Thanks for your patience.

Therese

 

[Duane Hookom]

So, when you open the subreport in design view and go to the Page Setup and
look at the Columns, what are you seeing? What is the number of columns? What
is the detail width and column size? What is the Column Layout?

How wide is the main report?
How wide is the subreport control on the main report?

 

[Therese G. Maddox]

This is a moment that I wish I could just sent you a pic through this
newsgroup.

In design view, the report is only an inch wide. It has a report header
which is 0.4688" in height, another grouping field (called employee id
header- just in case I decide to link it later) which is 0.4479" in height
(however it is not visible) a "field" header, which is 0.4375" in height and
the "text" header which is 0.3229" in height.

In the set up page, margins are set up as Top, .06, Bottom, .07, Left, .02
and Right, .02.

Page is Landscape, 11x17 (this is so large because anythng smaller and my
columns would have fallen off the page).

Columns: 15, row spacing 0", column spacing 0"
Column width 0",spacing, 0". Coumn size, .9", height: .2493" Layout: down
then across.

I had to fiddle with this to get it right.

It seems to me that if I could put the sub report on a smaller piece of
paper maybe it might work,

When I preview it in print preview as a sub report, it just looks like the
report view, not a multi column report within the main report. Basically,
the sub report's multi column functionality isn't working.

The main report is as big as it can get, which is 11 x 17 landscape, which
may be the issue.

Therese

 

[Duane Hookom]

I wonder if the level of detail records in your main report is nearly the
same as your subreport.

I would expect the results you are experiencing if for instance I had a
multi-column subreport of OrderDetails where I wanted to display the product,
unit price, and quantity. If my main report was based on the Orders table, I
would set the Link Master/Child to the OrderID fields. This should work as
expected.

If my main report was based on the Orders and the OrderDetails tables, it
would be a mistake to set the Link Master/Child to the OrderID,ProductID. My
resulting display with the subreport in the detail section would look like a
single column report.