Solved

How do I place totals derived from a header into the report footer?

Posted on 2015-02-02
7
100 Views
Last Modified: 2015-02-06
I have several headers on my report: Supervisor, team (multiple teams under one supervisor), and employee. Under the employee name his training is listed. How do I total the number of employees, from the employee header, and place the total in the report footer?

The details on the report are:

Header:  User_ID Header
Text Box:  Name User_ID1
Text Box: Control Source: User_ID

In the Report Footer, I put a text box with, =Sum([User_ID])

For this, I got the error message, "Data type mismatch in criteria expression."

When I changed the text box by adding a 1 at the end of User_ID, I got a "Enter Parameter Value" dialog box for User_ID1. Clicking OK, gave me a blank text box in the Report Footer in the report view.

Changing the Format property of the text box to Standard and 0 decimal places produced the same result. Visible is "Yes."
0
Comment
Question by:David Bigelow
7 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40585083
=Count([User_ID]) would seem to be the appropriate function.
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 40585106
Well, at least that returned an answer!

But it's giving the count of all the skills for all employees, instead of the number of employees. I say the total number of skills, but maybe it's counting the total records in the report. 2,410 is too many for me to comb through and differentitate between total skills and total records. Either way, it's much more than the number of employees listed. Maybe there's something I don't understand about how reports and counts are made to work together (obviously, or I wouldn't be posting, :o)). I would think that if I use the field which references the employee, which I am using, then that should give me the total employees.

Any other suggestions?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40585146
Sorry, I was too hasty.

I'd consider either
a) redesigning the report so that the main report datasource only includes each employee once, and insert the training for each individual in as a subreport in the detail section (linked on USER_ID). In such a report design you could use =Count([User_ID]) in the report footer.
or
b) leaving report as is and using a subreport in the report footer to display the number of employees and other aggregated totals. this would be based on a second query that counts the grouped records from the main query
i.e.
if your detailed report is qryReport, your summary subreport would be based on
SELECT COUNT(*) FROM (SELECT USER_ID FROM qryReport GROUP BY USER_ID)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 35

Expert Comment

by:PatHartman
ID: 40585415
Add a Footer section for the User_Id.
Add a hidden field.  Name it txtUserCount
Set its ControlSource to ---   =1
Set its Group property to OverAll
In the footer, add a control to display the total.
Set its ControlSource to
=txtUserCount

You can make the footer section small enough to not get in the way.  You need to put this control in a footer rather than the header because the footer will occur only once for the section whereas the header may print more than once if the section covers multiple pages.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40586640
...or perhaps you could post a sample database, ...it is difficult for me to understand exactly what it is you are seeking here.

Show us the report,
Then show us ,graphically, what you need the exact output be in the report.
0
 
LVL 1

Author Comment

by:David Bigelow
ID: 40594256
I haven't forgotten this. I'm shooting for a perfect close record. Right now, another issue that I'm working on will affect my ability to test these answers.
0
 
LVL 1

Author Closing Comment

by:David Bigelow
ID: 40594934
I was looking at my settings and information incorrectly. This is the solution. Sorry about the extra work, Experts. You guys are an amazing team.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

803 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