[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

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

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
David Bigelow
Asked:
David Bigelow
1 Solution
 
SimonCommented:
=Count([User_ID]) would seem to be the appropriate function.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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
 
SimonCommented:
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
Technology Partners: 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!

 
PatHartmanCommented:
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
 
Jeffrey CoachmanCommented:
...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
 
David BigelowStaff Operations SpecialistAuthor Commented:
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
 
David BigelowStaff Operations SpecialistAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now