DataBase - Totaling Multiple Subreports

Hi,

I have a main report with four subreports and three of those have subreports
on them.  All are linked by salesperson and I need a grand total of each
department from subreport1, subreportof2, subreportof3 and subreportof4.
I am not sure how to go about this as everything I have tried has not worked -
name error, same total repeated for each department, etc. The fields on each
summary report in the department group are deptname and deptsumgp07,
deptsumgp08 and deptsumgp09.  For clarity (I hope), I 've liste the
following setup:

Main report:                Groups Salesperson, Customer, Department
Subreport1:             Summary totaling Department group

Subreport2:             Groups Salesperson, Customer, Department
SubreportOf2:     Summary totaling Department group

Subreport3:             Groups Salesperson, Customer, Department
SubreportOf3:     Summary totaling Department group

Subreport4:             Groups Salesperson, Customer, Department
SubreportOf4:     Summary totaling Department group

If anyone can help with this, I would greatly appreciate it.  I have spent a
considerable amount of time and am at a loss how to pull all this together.
Thanks in advance,
Pam

You lost me about what is in the main report and where you
want the total to appear.

You should be able to get a value from your subsubreports in
a main report yrccy box by using this kind of reference:
=IIf(Subreport1.Report.SubreportOf1.Report.HasData,
Subreport1.Report.SubreportOf1.Report.report1totaltextbox,
0) + IIf(Subreport1....) + ...

--
Marsh
MVP [MS Access]

Marsh,

I have tried putting the calculation in the salesman footer and in the
department footer and neither will give a break down of the department groups
as they are on the summaries.  I need a grand total of each department from
each summary. I tried using another report with just salesman and department
descriptions as the groups, placing just the summaries in the department
footer and then inserting the calculation (mainsummary.report.has data,
mainsummary.report!gp09,0) in the footer, but it only returns the first entry
in the department group in that particular summary.

Is this even possible?  Is there another alternative I should try?

Pam

Well, I still do not understand what you want so I cannot say
if it is possible or not.

Originally, you said you had the four subreports in the
salesman footer and the text box with the long expression in
the same footer.  I would expect the long expression you
used to work to get a total for the salesman.

Now, you are talking about some kind of department summary
and I have no idea what that means.  If you want some kind
of thing in the salesman footer that lists all departments
with their subtotal, then that is a different problem that I
have not been able to deduce any details about.

OTOH, if you have a department subreport in the
salesman-department group footer, then another text box in
the department footer can use a similar expression to bring
the department total to the main report.  Then you can use
that text box's RunningSum property to add up the total for
all the departments for the salesman.

--
Marsh
MVP [MS Access]

I am sorry my explanations are not clear enough. You've provided some excellent
help with past problems and I really appreciate it.  Please let me try once
more to explain.  Originally, I had a summary in the salesman footer that
would summarize by department all his sales. I had only one summary and it
worked well.  Things have changed and now I have three additional summaries
grouped by department for three different types of sales. What I need is a
grand total for each department from all the summaries  regardless of type of
sale is falls under.  The reason I said I had it in salesman footer is
because that is where the subreports of summaries were originally.  The total
would not give me the department totals I needed there, so I tried putting
them in department footer.  It does not work either.  The calculation 'if
report has data, return data from field' gives only the first department
amount.
I hope this makes sense.
Again, I thank you for your time and help.
Pam



--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1

Let's stop talking about what you used to have and only
discuss what you are trying to achieve.  Your use of the
word "summary" is not definitive enough for my mind to get a
grip on what you are trying to display in the various footer
sections.

I suspect that you need a new subreport in the salesman
footer to display the totals for all the departments.
Remember that the total calculations can be done in a query
used as the subreport's record source.  This would be
similar to the subreports you already have, just grouping by
fewer fields.



--
Marsh
MVP [MS Access]

Marsh,

I finally have it working as needed!! Thanks for the tip about the query
calculations. I usually try to do all my grouping calculations on the report,
but in this case it had to be done thru a series of queries.  You made me
think outside the box (or report)!!
Sorry for the confusion - I guess what may appear clear to one, really is not
to another.
Thanks again.
Pam


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/201001/1