Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group Header totals in Access 2010

Posted on 2015-02-13
6
Medium Priority
?
206 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 39

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 39

Accepted Solution

by:
PatHartman earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
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.

636 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