Totalling Row Data From Subreports

[bhipwell via AccessMonster.com]

I have a feeling I know the answer to this, but I want to make sure.

I have a report with a number of subreports. Each subreport draws from their
own query which performs a number of heavy calculations.

On the main report, I would like a column which sums the data from each row
of the subreports, like this:

Sub1 Sub2 Sub3 Sub4 Main
$2 $4 $3 $8 $17
$1 $2 $1 $3 $7

I am looking for the formula that will get me the "$17" and "$7" in the
example above. I have tried creating another subreport tied to a query which
brings together all the necessary data, however I get the "cannot open
anymore databases". I also need to have the numerous subreport for
visibility purposes.

My understanding is that you can only add data from subreports located in the
header and footer sections. Please let me know if there is a way to sum data
by rows from the detail section of a subport.

BH

 

[Jeff Boyce]

I've been putting together a report that seems like it's closely related.
Let me ask, though, are those "rows" specific to some underlying record(s)?

In my situation, I use queries to feed the subreports analogous to your
Sub1, Sub2, ... Since each of these also has instrinsic "row identifiers",
I build one more query that joins all the others together on that intrinsic
row identifier and does the math.

Then I built a sub-report that displays that math (i.e., the sum of the
amounts from each of the other subreports).

Hopefully, though, another newsgroup reader can offer a more elegant
solution...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhipwell via AccessMonster.com]

It sounds very similar to what I am doing. To answer your question, yes
there is an underlying record for each row in each subreport. The subreports
sit next to one another and are set up so that row 1 of Sub 1 has the same
record identifier as row 1 of Sub 2 (and so on and so forth).

My reason for multiple subreports if for visibility and movement. Not all
subreports will be displayed and it is much simplier to allow the sub report
justify to the left than to use a ton of "move" commands.

I can't do a "Query of queries" because I run into the "unable to open any
more databases" error. Two queries less, and it would work.

I was hoping for a way to simply lift the data off the subreport using report!
subreport!field, but I doesn't work for fields in the detail section of
subreports (it doesn't continually build rows).

Any other ideas? I am going to try a couple of other query builds to see if
I can rectify the problem. Hopefully soon, I really need a solution.

BH

 

[bhipwell via AccessMonster.com]

Just a thought...

Each of our subreports has its own query. Then we are considering creating a
"Query of queries." If we create the "Query of queries" first and have each
subreport run from that one big Query, would we be using more table handles
or less?

Basically, the question is...if you have multiple subreports pulling from the
single big query, are we opening up table handles for each subreport or just
once since it is the same query pulling from the tables?

Make sense? Thoughts?

BH

 

[Jeff Boyce]

I'm surprised you're getting the "too many..." error. How many subreports
are you building?

Is there a chance you could grab all the data in a single query, then use a
crosstab query to generate the columns?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhipwell via AccessMonster.com]

I am building 12 subreports. I could have each subreport from the same query.
My question (as posted later on this thread) is whether having 12 subreports
is equivalent to...

1. 12 subreports running 12 queries or...
2. 12 subreports running 1 query

What do you think?

Regarding the cross tab, I don't have any experience translating a cross tab
query into a report to format it for the end user. Plus, I need the ability
to hide and shift not applicable columns (or subreports as currently built).

BH

 

[Jeff Boyce]

I hope WE get some good additional input here. I'm wrestling with getting
mine to work as well.

Regards

Jeff Boyce
Microsoft Office/Access MVP