summing inside multiple groups

Hi experts,

how can i have do a sum inside group with different headings. For instance
i have Group headerA (Transactions) then detail and then its sum at the group footer. Then i have another groupheaderB (holdings) then detail and its sum at the group footer but when i put a text box in the footers such total transaction or total holdings in group footer a and group footer shows up in both footers. In other words it shud be someting like this. Thanks for ur help

GHA -transactions
GFA- (total transactions a heading) and its sum

GHB - Holdings
GFB- total holdings a heading) and its sum
Who is Participating?
Pigster14Connect With a Mentor IT ConsultantCommented:
Well, with you having two sets of headers and footers, I would expect for there to be some repeating.

Okay, let's back up.

What does your data look like behind this report?

Here is what I am thinking as an example: Let's say your data is in regards to objects that have in your table you have the following:

Object               Color1           Color2            Color3
Banana             Yellow
Hot Wheel Car   Green            Blue
Rainbow            Blue              Orange           Red

(This is a crazy example, but go with me on this.)

If you add groupings by Color1, Color2 and Color3.....some results will have one grouping. Another result would have two groupings and the last result would have three groupings.

What I am thinking is that depending what column(s) you are grouping on that some data may not have all the groupings if the what you are grouping by is null for that particular peice of data.

Does that make sense?


Pigster14IT ConsultantCommented:
Instead of a label, input a text field.

Here is a sample of one report I have...

In the header there is:

="Investor " & [Investor Number] & " Block " & [Investor Block ID]

In the [] it is referencing the field you are grouping buy. In this case, I have several headers and footers depending on which Investor I am showing detail information for.

Here is an example of a footer that has a count function in it.

="Inst Num: " & [Bank Number Sub] & " New Accounts: " & Count(*) & "  New Balance:  $" & Format(Sum([Bal Prin]),"Standard")

Again, it is in a text field and not a label.

Hope this helps.

Pigster14IT ConsultantCommented:
It appears you are already using text boxes instead of labels.....I guess I should read more carefully.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Pigster14IT ConsultantCommented:
I am way off base. Let me start over... :)

You show the way you would like it to appear, however I am guessing that with having the two groups of headers and footers as you state it is really coming out as follows:

GroupA Header
   GroupB Header
    GroupB Footer
GroupA Footer

Is that correct?

zafridiAuthor Commented:
no everything else is coming out rite the way i want it..its just at the footers i have to have different text such as
transactions total and holdings total..
im getting
groupA header
then group A footer
then group b header
then group b footer

which is fine i just wana be able to put different text next to the total in the footers
Pigster14IT ConsultantCommented:
So when you look at the design of your report, do you really only have one footer and header?

You just want to pass in something to signify that this first group is Transactions and the 2nd group is Holdings?

zafridiAuthor Commented:
no i have 2 headers and 2 footers
Pigster14IT ConsultantCommented:
Can you post what is in the text boxes for each of these headers and footers?

zafridiAuthor Commented:
Thats how it looks in the design
Gh1- Transactions
Gh2- holdings
Gf2 -transactions- numbers(sum of different columns)
Gf1-holdings- numbers(sum of different columns)

In preview i get this
gf2 -transactions -and its sum
gf1-holdings -and its sum
gf2 -transactions -and its sum
gf1-holdings -and its sum

My problem is where the group footers repeat themselves. each group footer comes once under its respective group i need some sort of suppression or i dont know..thanks

Lets say your data looks like:

Class  Type  No

In the Header you select Class
In the Detail you select Type and No
In the GroupFooter you select Type and Sum(No)
In the ReportFooter you select Sum(No)

make sure you are not putting the Sum(No) in the Report footer and expecting to see the GroupFooter value

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.