Grand Totals In Report Footer for Separate Groups


G

Guest

Hello,

I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)

Thanks very much for your help!
 
Ad

Advertisements

M

Marshall Barton

laknight said:
I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)



Create a new query based on the report's query that groups
and calculates the desired totals. Then create a new report
based on the new query and drop it in the footer of your
existing report.
 
G

Guest

I guess I left out one very important piece of info: I have a form setup as
user input for date range to specify a limit to the results of the report. If
I setup a separate query with a second subreport, my grand totals calcs will
not be based off of the filtered info.


Marshall Barton said:
laknight said:
I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)



Create a new query based on the report's query that groups
and calculates the desired totals. Then create a new report
based on the new query and drop it in the footer of your
existing report.
 
Ad

Advertisements

M

Marshall Barton

That's why I said to use the report's record source query as
the basis for the summary totals query.

If you were applying the date range filter via the
OpenReport method's WhereCondition argument, you will have
to forgo that approach and use references to the text boxes
directly in the main report query's criteria.
--
Marsh
MVP [MS Access]

I guess I left out one very important piece of info: I have a form setup as
user input for date range to specify a limit to the results of the report. If
I setup a separate query with a second subreport, my grand totals calcs will
not be based off of the filtered info.


Marshall Barton said:
Create a new query based on the report's query that groups
and calculates the desired totals. Then create a new report
based on the new query and drop it in the footer of your
existing report.
laknight said:
I am having trouble getting a Grand Total in the footer of my report to
calculate correctly.

I tried a Dsum expression but it was verrrryyyy sloooowww! It looked like
this (one for each Group):

=DSum(" [ORDER_TOTAL]
","SalesJournal_UnionInfo","[ACTIVITYGROUP]='OPENORDER'")

=DSum(" [ORDER_TOTAL] ","SalesJournal_UnionInfo","[ACTIVITYGROUP]='INVOICED'")

I believe it was working correctly but way too slow. So I'm looking for
alternatives.

My report is based off of the query SalesJournal_UnionInfo. First the
records on the report are grouped by SALESREP, then grouped by ACTIVITYGROUP
which is either "INVOICED" or "OPENORDER". In the ACTIVITYGROUP footer I have
the [ORDER_TOTAL] field set to =Sum(ORDER_TOTAL]) not as a running sum and
it's name is SUBTOTAL_CALC. This gives me the SUBTOTAL calculation per each
activity per each SALESREP.

How do I get the separate GRAND TOTALS of each ACTIVITY SUBTOTAL into the
Report Footer? (without using DSum?!)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top