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!
Course Of The Month
8 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Display # of Emp in a Column Report displayed in footer of report
Posted on 2013-01-03
I have created a report that uses columns for an ORG chart report. I need to display the total number of employees within each column. How do I incorporated this value so that it is displayed at the bottom of the report and not at the floating bottom of the column.
I also need to include a total of employees, broken down by the type of employee - ie a legend. to also be displayed on the bottom of the report.
Note: I have tried various approaches by including the count (ie. Count(Empl#) places in the Manager footer - this gave me the floating value depending on the height of the column values. Then I tried using this field as a hidden field and placing a text field in the Report footer that references that hidden field - but that failed.
Also tried using a subreport but I was unable to get the report to line up with the Manager's column in the main report.
Looking for any suggestions.
- sample from the original spreadsheet - which I am attempting to create in an Access Report.
I also need to include a total of employees, broken down by the type of employee - ie a legend. to also be displayed on the bottom of the report.
Note: I have tried various approaches by including the count (ie. Count(Empl#) places in the Manager footer - this gave me the floating value depending on the height of the column values. Then I tried using this field as a hidden field and placing a text field in the Report footer that references that hidden field - but that failed.
Also tried using a subreport but I was unable to get the report to line up with the Manager's column in the main report.
Looking for any suggestions.
- sample from the original spreadsheet - which I am attempting to create in an Access Report.
[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
-
15
14
29 Comments
Active today
<<How do I incorporated this value so that it is displayed at the bottom of the report and not at the floating bottom of the column.>>
I would use the page/report footer as appropriate and keep your own counts.
In the reports module at the top, dim variables to keep the totals.
In the page/report header OnFormat event, init those variables to 0.
In the detail section, add to those variables in the OnFormat event doing:
If FormatCount = 1 then
' Add to variables. Use the left property to determine the column your in.
End If
In the page/report footer, push the value of your variables into unbound text controls in the OnFormat event.
Do this with one total first to get it working, then add your other totals as needed (maybe use an array).
Jim.
I would use the page/report footer as appropriate and keep your own counts.
In the reports module at the top, dim variables to keep the totals.
In the page/report header OnFormat event, init those variables to 0.
In the detail section, add to those variables in the OnFormat event doing:
If FormatCount = 1 then
' Add to variables. Use the left property to determine the column your in.
End If
In the page/report footer, push the value of your variables into unbound text controls in the OnFormat event.
Do this with one total first to get it working, then add your other totals as needed (maybe use an array).
Jim.
for some reason my report now has 3 footers for the same field, how do I remove the unused footers. see attached
I may need a little more to help me get started:
<<In the reports module at the top, dim variables to keep the totals.
In the page/report header OnFormat event, init those variables to 0.
In the detail section, add to those variables in the OnFormat event doing:
If FormatCount = 1 then
' Add to variables. Use the left property to determine the column your in.
End If
In the page/report footer, push the value of your variables into unbound text controls in the OnFormat event.>>
<<In the reports module at the top, dim variables to keep the totals.
In the page/report header OnFormat event, init those variables to 0.
In the detail section, add to those variables in the OnFormat event doing:
If FormatCount = 1 then
' Add to variables. Use the left property to determine the column your in.
End If
In the page/report footer, push the value of your variables into unbound text controls in the OnFormat event.>>
Option Compare Database
Option Explicit
Public nMgrCt As Long
Private Sub MgrNameFooter_Print(Cancel As Integer, PrintCount As Integer)
Dim intGetVal As Integer
intGetVal = Me.Section(2).NewRowOrCol
End Sub
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Set nMgrCt = 0
End Sub
Active today
<<for some reason my report now has 3 footers for the same field, how do I remove the unused footers. see attached>>
Look in sorting and grouping and see what you have defined. That looks weird.
Option Compare Database
Option Explicit
Public nMgrCt(3) As Long
' This is OK.
Private Sub PageHeaderSection_Format(C
Erase nMgrCt
End Sub
' Think you want the page footer if your initing in the page header or is this where your picking your total up?
Private Sub MgrNameFooter_Print(Cancel
If PrintCount = 1 then
' Not sure of the exact formula for your column # but say your columns are 2" wide. That's 2880 twips each. So something like:
intCol = FIX(Me.left / 2880) + 1
' Should give you the correct column number (you may need to play with that a bit).
' Once you know the column, store your totals.
nMgrCt(intCol) = nMgrCt(intCol) + <value>
End If
End Sub
Private Sub PageFooterSection_Format(C
Me![<somecontrol1>] = nMgrCt(1)
Me![<somecontrol2>] = nMgrCt(2)
Me![<somecontrol3>] = nMgrCt(3)
End Sub
Look in sorting and grouping and see what you have defined. That looks weird.
Option Compare Database
Option Explicit
Public nMgrCt(3) As Long
' This is OK.
Private Sub PageHeaderSection_Format(C
Erase nMgrCt
End Sub
' Think you want the page footer if your initing in the page header or is this where your picking your total up?
Private Sub MgrNameFooter_Print(Cancel
If PrintCount = 1 then
' Not sure of the exact formula for your column # but say your columns are 2" wide. That's 2880 twips each. So something like:
intCol = FIX(Me.left / 2880) + 1
' Should give you the correct column number (you may need to play with that a bit).
' Once you know the column, store your totals.
nMgrCt(intCol) = nMgrCt(intCol) + <value>
End If
End Sub
Private Sub PageFooterSection_Format(C
Me![<somecontrol1>] = nMgrCt(1)
Me![<somecontrol2>] = nMgrCt(2)
Me![<somecontrol3>] = nMgrCt(3)
End Sub
Active today
BTW, you should pickup a copy of the Access Developers Handbook published by Sybex. Tons of code in there, one of which is a class object to handle app the prtMip chores. Probably could have saved a ton of time with this if you had had it.
It's great for when you get into odd-ball stuff like this.
Jim.
It's great for when you get into odd-ball stuff like this.
Jim.
when I attempt to remove the misc. footers it crashes my system, so I may have to start over with my back up copy of the report.
K
K
Option Compare Database
Option Explicit
Public nMgrCt(3) As Long
Won't compile - get Constants, Fixed-length strings, arrays, user-defined types & declare statement not allowed as Public members of object modules.
Option Explicit
Public nMgrCt(3) As Long
Won't compile - get Constants, Fixed-length strings, arrays, user-defined types & declare statement not allowed as Public members of object modules.
by the way I do have a old version 2000 of the book you mentioned, it wasn't much help. I am so use to looking things up on the web that I forget to check the actual books.
k
k
My latest attempt - please review, I am only getting the value of Zero to be displayed.
thanks,
K
OrgChartSample.zip
thanks,
K
OrgChartSample.zip
Active today
OK, your all set. Totals are working and I got rid of that orphand header on page 2 (totalling needed to be done differently because of that and that your doing columns).
It was caused by the fact that the column break was set for after the footer and that was forcing the page break.
I changed that to none and set the header to BeforeSection. You don't get a blank column because it knows it can't back up on the first page/first column.
Spacing on the column totals needs to be adjusted yet.
Jim.
OrgChartSample.zip
It was caused by the fact that the column break was set for after the footer and that was forcing the page break.
I changed that to none and set the header to BeforeSection. You don't get a blank column because it knows it can't back up on the first page/first column.
Spacing on the column totals needs to be adjusted yet.
Jim.
OrgChartSample.zip
Thanks Jim for all your hard work it looks great, one minor issue - the number fields in the footer is there a way to automate the number of fields depending on the number of columns - since my column count will change depending on the Unit chief's org size.
Karen
Karen
Active today
Karen,
In the OnFormat of the page footer, look at the totals in the array. If the total is zero, set the corresponding controls visible property to false.
Jim.
In the OnFormat of the page footer, look at the totals in the array. If the total is zero, set the corresponding controls visible property to false.
Jim.
Active today
Actually, do a loop on the array and do:
For intK = 1 to 9
If nMgrCt(intK)>0 then
<control>.visible = True
Else
<control>.visible = False
End If
Next intK
That way it will be correct for each page.
The controls are named sequentially, so you can use the format Report!("name" & intK).Visible =
(don't rememeber the control names off-hand and I already deleted all your stuff from here).
Jim.
For intK = 1 to 9
If nMgrCt(intK)>0 then
<control>.visible = True
Else
<control>.visible = False
End If
Next intK
That way it will be correct for each page.
The controls are named sequentially, so you can use the format Report!("name" & intK).Visible =
(don't rememeber the control names off-hand and I already deleted all your stuff from here).
Jim.
Private Sub PageFooterSection_Format(C
Dim intk As Integer
For intk = 1 To 11
If nMgrCt(intk) > 0 Then
Reports![Report1]![txtEmpC
Else
txtEmpCt1 " & intK.Visible = false"
End If
Next intk
Dim intk As Integer
For intk = 1 To 11
If nMgrCt(intk) > 0 Then
Reports![Report1]
Ok after the first pass the intk value = 1, but it still wants to return visible =false
Private Sub PageFooterSection_Format(C
Dim intk As Integer
For intk = 1 To 11
If nMgrCt(intk) = 0 Then
Report("txtEmpCt" & intk).Visible = False
Else
Report("txtEmpCt" & intk).Visible = True
End If
Next intk
End Sub
Active today
All bets are off...the last upload you have contains the original report, which has all the funky footers in it.
That report was corrupt. I rebuilt the whole thing from scratch and called it report1 in my upload.
That's what you need to be using.
Jim.
That report was corrupt. I rebuilt the whole thing from scratch and called it report1 in my upload.
That's what you need to be using.
Jim.
Active today
This BTW:
Private Sub PageFooterSection_Format(C
Dim intk As Integer
For intk = 1 To 9
If nMgrCt(intk) = 0 Then
Report("txtEmpCt" & intk).Visible = False
Else
Report("txtEmpCt" & intk).Visible = True
Report("txtEmpCt" & intk) = nMgrCt(intk)
End If
Next intk
End Sub
Is the final code I used and it works fine here.
Jim.
Private Sub PageFooterSection_Format(C
Dim intk As Integer
For intk = 1 To 9
If nMgrCt(intk) = 0 Then
Report("txtEmpCt" & intk).Visible = False
Else
Report("txtEmpCt" & intk).Visible = True
Report("txtEmpCt" & intk) = nMgrCt(intk)
End If
Next intk
End Sub
Is the final code I used and it works fine here.
Jim.
sorry, but your version does not display multiple columns also I need a total of up to 11 fields for the footer for the employee count.
K
K
Active today
Yes it does, but I left the filter in from when I was testing the column total blanking. Open up report1 and set the filteron to no and blank out the filter.
Also, your original report only had 9 columns in it. With that said, there's nothing stopping you for squeezing in a couple of more other then layout.
Jim.
Also, your original report only had 9 columns in it. With that said, there's nothing stopping you for squeezing in a couple of more other then layout.
Jim.
Featured Post
Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 9 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.