Solved

Group Header totals in Access 2010

Posted on 2015-02-13
6
183 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
[X]
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 Comments
 
LVL 18

Expert Comment

by:Simon
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 38

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 38

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

691 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