?
Solved

Group Header totals in Access 2010

Posted on 2015-02-13
6
Medium Priority
?
196 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 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