Solved

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

Posted on 2015-02-02
7
97 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:
SimonAdept 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:SimonAdept
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now