summing inside multiple groups

Posted on 2006-11-24
Last Modified: 2012-06-22
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
Question by:zafridi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3

Expert Comment

ID: 18008622
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.


Expert Comment

ID: 18008629
It appears you are already using text boxes instead of labels.....I guess I should read more carefully.

Expert Comment

ID: 18008658
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?

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Author Comment

ID: 18008683
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

Expert Comment

ID: 18008702
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?


Author Comment

ID: 18008847
no i have 2 headers and 2 footers

Expert Comment

ID: 18008876
Can you post what is in the text boxes for each of these headers and footers?


Author Comment

ID: 18008962
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


Accepted Solution

Pigster14 earned 250 total points
ID: 18009049
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?


LVL 44

Expert Comment

ID: 18010197
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


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question