Solved

summing inside multiple groups

Posted on 2006-11-24
10
238 Views
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 b..it shows up in both footers. In other words it shud be someting like this. Thanks for ur help

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

GHB - Holdings
details
GFB- total holdings a heading) and its sum
0
Comment
Question by:zafridi
  • 6
  • 3
10 Comments
 
LVL 8

Expert Comment

by:Pigster14
Comment Utility
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.

Thanks.
0
 
LVL 8

Expert Comment

by:Pigster14
Comment Utility
It appears you are already using text boxes instead of labels.....I guess I should read more carefully.
0
 
LVL 8

Expert Comment

by:Pigster14
Comment Utility
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
       Detail
    GroupB Footer
GroupA Footer

Is that correct?



0
 

Author Comment

by:zafridi
Comment Utility
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
detail
then group A footer
then group b header
detail
then group b footer

which is fine i just wana be able to put different text next to the total in the footers
0
 
LVL 8

Expert Comment

by:Pigster14
Comment Utility
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?

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:zafridi
Comment Utility
no i have 2 headers and 2 footers
0
 
LVL 8

Expert Comment

by:Pigster14
Comment Utility
Can you post what is in the text boxes for each of these headers and footers?

Thanks.
0
 

Author Comment

by:zafridi
Comment Utility
Thats how it looks in the design
Gh1- Transactions
Gh2- holdings
detalils
Gf2 -transactions- numbers(sum of different columns)
Gf1-holdings- numbers(sum of different columns)

In preview i get this
gh2-transactions
details
gf2 -transactions -and its sum
gf1-holdings -and its sum
gh1
details
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 header..do i need some sort of suppression or i dont know..thanks


0
 
LVL 8

Accepted Solution

by:
Pigster14 earned 250 total points
Comment Utility
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 color....so 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?

Thanks.



0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now