Totaling Issue for Group Non-Bound Value

[FlBrent via AccessMonster.com]

I've beat my head against the wall on this and can't come up with a viable
solution.

I have a report that has sales data for both individual dealers and the
entire industry, grouped by sales territory. Each sales territory has totals
for individual brands. See below for an example:

Territory Brand Dealer Industry
Boston Mista 1 8
Boston Johnston 3 8
Boston Bills 2 8


Now, as you can see, each Brand has the same Industry total. This is because
each brand
tracks all sales for the industry so the #'s will always be the same. So...
the actual Industry sales for Boston territory for this month would be 8
total. I can get this at the group level by dividing the (sum of Industry
Sales) by the (Group count of rows). My problem is that I can't sum this
figure in the Report Footer section, which I need to do.

I hope this makes even remote sense, as it's not very easy to explain.
Thanks for any help.

 

[Klatuu]

Your basic setup is incorrect. Your group totals should be a control in the
group footer that is the sum of the field you want to total. For example, to
total sales for a territory, you would first have a control in the detail
section bound to a field in the report's record source that would provide the
detail information. So the control source would be [InvoiceAmount]

IN the Group Footer you would use =Sum([InvoiceAmount]) in a control source
That will present the sum of all invoices for a territory.

The same is true for a report total. It would be the exact same formula
because the report knows the control is in the report footer and will sum
every row in the report's record source.

 

[FlBrent via AccessMonster.com]

That will not solve this issue. If I just wanted the SUM of the detail
control, then that
would be fine, but I need to actually get an AVG at the group level to get
the correct #'s for the group. I want to then SUM the AVG for each group
at the Report Footer.

Your basic setup is incorrect. Your group totals should be a control in the
group footer that is the sum of the field you want to total. For example, to
total sales for a territory, you would first have a control in the detail
section bound to a field in the report's record source that would provide the
detail information. So the control source would be [InvoiceAmount]

IN the Group Footer you would use =Sum([InvoiceAmount]) in a control source
That will present the sum of all invoices for a territory.

The same is true for a report total. It would be the exact same formula
because the report knows the control is in the report footer and will sum
every row in the report's record source.

I've beat my head against the wall on this and can't come up with a viable
solution.

[quoted text clipped - 18 lines]

I hope this makes even remote sense, as it's not very easy to explain.
Thanks for any help.

 

[Marshall Barton]

I've beat my head against the wall on this and can't come up with a viable
solution.

I have a report that has sales data for both individual dealers and the
entire industry, grouped by sales territory. Each sales territory has totals
for individual brands. See below for an example:

Territory Brand Dealer Industry
Boston Mista 1 8
Boston Johnston 3 8
Boston Bills 2 8


Now, as you can see, each Brand has the same Industry total. This is because
each brand
tracks all sales for the industry so the #'s will always be the same. So...
the actual Industry sales for Boston territory for this month would be 8
total. I can get this at the group level by dividing the (sum of Industry
Sales) by the (Group count of rows). My problem is that I can't sum this
figure in the Report Footer section, which I need to do.

You should have a group header section for the industry and
add a text box (named txtRunTotal) to the group header
section that is also bound to the industry total field. Set
this text box's Running Sum property to Over All and make it
invisible if you do not want to see it in the report.

The report footer text box can then display the grand total
by using the expression:
=txtRunTotal

 

[Klatuu]

Sorry, maybe I didn't read your post correctly.
That is an interesting problem. You can use th AVG function at the group
level to get the average. As you say, the difficulty is summing the
averages. I would have to do some experimenting.
--
Dave Hargis, Microsoft Access MVP

That will not solve this issue. If I just wanted the SUM of the detail
control, then that
would be fine, but I need to actually get an AVG at the group level to get
the correct #'s for the group. I want to then SUM the AVG for each group
at the Report Footer.

Your basic setup is incorrect. Your group totals should be a control in the
group footer that is the sum of the field you want to total. For example, to
total sales for a territory, you would first have a control in the detail
section bound to a field in the report's record source that would provide the
detail information. So the control source would be [InvoiceAmount]

IN the Group Footer you would use =Sum([InvoiceAmount]) in a control source
That will present the sum of all invoices for a territory.

The same is true for a report total. It would be the exact same formula
because the report knows the control is in the report footer and will sum
every row in the report's record source.

I've beat my head against the wall on this and can't come up with a viable
solution.

[quoted text clipped - 18 lines]

I hope this makes even remote sense, as it's not very easy to explain.
Thanks for any help.

 

[FlBrent via AccessMonster.com]

Marshall,
That worked like a charm. I've tried about every other variation of
that, but it never
worked. Any reason, just curious, as to why it has to be in the header area?

Anyway, Thanks so much for you help!!!

Brent

I've beat my head against the wall on this and can't come up with a viable
solution.

[quoted text clipped - 15 lines]

Sales) by the (Group count of rows). My problem is that I can't sum this
figure in the Report Footer section, which I need to do.

You should have a group header section for the industry and
add a text box (named txtRunTotal) to the group header
section that is also bound to the industry total field. Set
this text box's Running Sum property to Over All and make it
invisible if you do not want to see it in the report.

The report footer text box can then display the grand total
by using the expression:
=txtRunTotal

 

[Marshall Barton]

If you put it in the detail section it would be essentially
the same as using Sum. Putting it in the group header will
only add the value once per group instead of for every
detail.

It could just as well go in the group footer section. I was
only guessing that you were more likely to have other uses
for a group header.
--
Marsh
MVP [MS Access]

That worked like a charm. I've tried about every other variation of
that, but it never
worked. Any reason, just curious, as to why it has to be in the header area?

I've beat my head against the wall on this and can't come up with a viable
solution.

[quoted text clipped - 15 lines]

Sales) by the (Group count of rows). My problem is that I can't sum this
figure in the Report Footer section, which I need to do.

You should have a group header section for the industry and
add a text box (named txtRunTotal) to the group header
section that is also bound to the industry total field. Set
this text box's Running Sum property to Over All and make it
invisible if you do not want to see it in the report.

The report footer text box can then display the grand total
by using the expression:
=txtRunTotal