Solved

summing inside multiple groups

Posted on 2006-11-24
10
264 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
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.

Thanks.
0
 
LVL 8

Expert Comment

by:Pigster14
ID: 18008629
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
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
       Detail
    GroupB Footer
GroupA Footer

Is that correct?



0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:zafridi
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
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
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?

0
 

Author Comment

by:zafridi
ID: 18008847
no i have 2 headers and 2 footers
0
 
LVL 8

Expert Comment

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

Thanks.
0
 

Author Comment

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

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

856 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