Solved

Group Header totals in Access 2010

Posted on 2015-02-13
6
147 Views
Last Modified: 2015-03-17
I have a Access 2010 report with a group header. I am adding a total column to the end of the group header row. I am just adding up the other columns on the group header row to get this total. The problem is that the total has the correct value in one row but the others are blank. How can I get a total for every row in a group header row?
0
Comment
Question by:LeLeBrown
6 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40609173
>The problem is that the total has the correct value in one row but the others are blank

How many rows in your group header? I'd have thought one was normal (for each group).
Can you post a screenshot to clarify the problem please.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40609823
If your calculation is in the ControlSource and the control is in the same section, referencing the control name should work.  However, Access doesn't retain values for unbound controls so if you are referencing unbound controls outside of the section where they occur, your results will be unpredictable.  Instead, you have to do the calculation again.
So
ctl1
=Sum(fldA + fldB)
ctl2
=Sum(fldC)

Instead of =ctl1 + ctl2
You may have to use:
=Sum(fldA + fldB) + Sum(fldC)
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40609830
If all else fails, you could make a totals query to get the relevant totals, and place a subreport bound to it in the header.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40609977
Remember that typically the group *Footer* is used for totals, ...not the group header.
Because of the Order in which the Report events fire, you may not get *reliable* totals in a group header.
am adding a total column to the end of the group header row. I am just adding up the other columns on the group header row to get this total.
?
This is confusing to us.
Can you clearly explain exactly what you have in the group header, and why?
Either post a screenshot of the report in Design view and Print preview, ...or post a sample database.
Perhaps you are going about this in the wrong way, ..and a more efficient design can be proposed...
Typically a report header/footer will only contain "Controls" (that summarize), ...not "Columns".

For reference, a sample db is attached with the same total in the group header and footer.

JeffCoachman
Database68.mdb
0
 

Author Comment

by:LeLeBrown
ID: 40617592
What was asked for was a variation on an existing report. The report is based on a crosstab query. A report structure is below. They want to see:
  in the group header various crime types. The detail shows that crime type further broken down below it (ex: Crime type Burglary - it can be broken out in the detail as Burg residence, burg commercial, burg shed etc...). The columns will be the area where it occurred. Below is the report sample with HR being the Header Row and DR Detail row. Crime Total is what I am trying to add for the HR only. I tried just adding up A1+A2+A3+A4+A5 for the Crime Total. When I do that, I get the correct totals for Larceny but I get spaces for Burg and Assault.
 
Crime Type          A1  A2  A3 A4 A5 Crime Total
HR Burg                                                29   (total for all Burglary)
 DR  Burg Res        3    5    8    0  2    
 DR  Burg Comm   6    2    0    3  0    
HR Larceny                                          
 DR  Larc veh         8    7    5    0   2     48
 DR  Larc Shop      4    6    4    3   9
HR Assault
 DR Simple            0    1     0   2   0     4
 DR Felony            0    0     0   1   0
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40617667
Reports are a sequential process.  If it prints first, it calculates first so trying to include a total which isn't calculated until all the rows are read and printed in the header which prints first, is a problem.    I would solve it by creating a separate totals query.  Then join the report's query to the totals query.  That way, you'll already have the total when you print the header and you won't have to do any coding or multiple processing to make it happen.  I didn't look at the example Jeff posted so that may in fact what he gave you an example of.  If not, the totals query is another option for you.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 73
Access recordset not updateable 8 39
Help with SQl and UNION 3 19
Syntax using Declare 3 16
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

16 Experts available now in Live!

Get 1:1 Help Now