Solved

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

Posted on 2015-02-02
7
99 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

862 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