?
Solved

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

Posted on 2015-02-02
7
Medium Priority
?
105 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
[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
7 Comments
 
LVL 18

Accepted Solution

by:
Simon earned 2000 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
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!

 
LVL 38

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

770 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