?
Solved

Access Report Sum Error from Crosstab Query

Posted on 2008-11-14
8
Medium Priority
?
717 Views
Last Modified: 2013-11-29
Hi Experts,

I am having problems adding up totals in my report colums.  What I have is a query that does some basic selects, then a crosstab query groups the data I need for me and counts things up nicely.  I can then use the crosstab query as the control source for the report.  My data isin't very complex:
Manager name            Station               Compliant               Non Compliant
so the manger names are grouped together so the numeric values in Compliant and Non Compliant are grouped counts by the manager name.  On my report in the Page footer or in the report footer I would like to sum the compliant and the Non Compliant over all.  Not by the manager, but just add up everything in the column.
I can get all the information to display correctly except the totals where I get #Error on the report.  I thought it was possibly a spelling mistake (in my page footer I have a text box with "=sum(Nz(Compliant))") but that was not the case.  The text box that I want to sum up is called Compliant in the detail header.  There are some null values in the column so I thought I needed the Nz.  I also tried to sum the control source value which is called "0" zero

Not sure what I am doing wrong, I am new to access and report building
0
Comment
Question by:Aaron Goodwin
  • 4
  • 4
8 Comments
 

Author Comment

by:Aaron Goodwin
ID: 22959325
Here are some screenshots minus the managers names.  I also threw in a running sum column just to see if things would add up.....and that works ok.  I'm not sure why I can't get the totals to work in the report

thanks!
Errors.doc
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22959381
<The text box that I want to sum up is called Compliant in the detail header>
Make sure you are using the name of the underlying filed (not a control name) in your sum statement.

Using the name of a control will definitely give you an error.

Also, if your field name contains spaces or other special characters, use brackets.

In other words:

"=sum(Nz([Some Field Name From the Report's Recordsource]))"
0
 

Author Comment

by:Aaron Goodwin
ID: 22959405
Thanks for the idea, but I Tried that too

In other words:

"=sum(Nz([Some Field Name From the Report's Recordsource]))"

in my case the record source is actually called "0"  zero


"=sum(Nz([0]))"
still returns an #error
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 22959447
One other thought -

You *might* need to do this in the Report Footer - not the page footer.

I'm not positive about that, though.  I generally prefer to automate Excel reports, and don't use Access reports often.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22959463
And finally - just for yucks -

If that fails, try the same thing using a different field name.  [0] and [1] are unconventional field names, and that could be messing things up.
0
 

Author Comment

by:Aaron Goodwin
ID: 22959486
There ya go.  Thank you very much.  I had tried something similar with no luck...I think I was using the textbox values.  And not the control source values.  You rock
0
 

Author Closing Comment

by:Aaron Goodwin
ID: 31516767
Thanks for the help, just couldn't see what was going wrong as I was staring at it for so long.  Appreciate it!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22959522
Glad to help out :-)
0

Featured Post

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.

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…
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 …
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 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