Solved

Display # of Emp in a Column Report displayed in footer of report

Posted on 2013-01-03
29
321 Views
Last Modified: 2013-02-26
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.1 - sample from the original spreadsheet - which I am attempting to create in an Access Report.
0
Comment
Question by:Karen Schaefer
  • 15
  • 14
29 Comments
 
LVL 57
ID: 38740930
<<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.
0
 

Author Comment

by:Karen Schaefer
ID: 38740990
for some reason my report now has 3 footers for the same field, how do I remove the unused footers.  see attachedt
0
 

Author Comment

by:Karen Schaefer
ID: 38741009
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.>>

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

Open in new window

K
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 38741180
<<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(Cancel As Integer, FormatCount As Integer)

   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 As Integer, PrintCount As Integer)

  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(Cancel As Integer, FormatCount As Integer)

   Me![<somecontrol1>] = nMgrCt(1)
   Me![<somecontrol2>] = nMgrCt(2)
   Me![<somecontrol3>] = nMgrCt(3)

End Sub
0
 
LVL 57
ID: 38741187
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.
0
 

Author Comment

by:Karen Schaefer
ID: 38741193
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
0
 

Author Comment

by:Karen Schaefer
ID: 38741337
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.
0
 

Author Comment

by:Karen Schaefer
ID: 38741342
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
0
 
LVL 57
ID: 38741387
<<Public nMgrCt(3) As Long>>

 Sorry, that should be:

 Dim nMgrCt(3) as Long

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 38741388
see error msg
ts
0
 

Author Comment

by:Karen Schaefer
ID: 38741409
sample err msg
0
 

Author Comment

by:Karen Schaefer
ID: 38741561
My latest attempt - please review, I am only getting the value of Zero to be displayed.

thanks,
K
OrgChartSample.zip
0
 
LVL 57
ID: 38741759
Working on it...

Jim.
0
 
LVL 57
ID: 38742042
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Karen Schaefer
ID: 38742057
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
0
 
LVL 57
ID: 38742091
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.
0
 
LVL 57
ID: 38742110
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.
0
 

Author Comment

by:Karen Schaefer
ID: 38742178
whats wrong wtiht this syntax

       (Reports![Report1]![txtEmpCt] " & intK.Visible) = True"
0
 

Author Comment

by:Karen Schaefer
ID: 38742181
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
Dim intk As Integer

For intk = 1 To 11
    If nMgrCt(intk) > 0 Then
       Reports![Report1]![txtEmpCt]  " & intK.Visible = True"
    Else
       txtEmpCt1 " & intK.Visible = false"
    End If
Next intk
0
 
LVL 57
ID: 38742203
Report("txtEmpCt" & intK).Visible  = True

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 38742261
My current code with your recommendation does not return any values - they are not visible.

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
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

I tried it with both >0 and < 0 - tried switching the order of the true/false.

I verified that the fields do exists on the report - I added the additional 2 fields.
OrgChartSample.zip
0
 
LVL 57
ID: 38742269
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

Jim.
0
 

Author Comment

by:Karen Schaefer
ID: 38742334
Jim

Ok after the first pass the intk value = 1, but it still wants to return visible =false

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
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
sa
0
 
LVL 57
ID: 38743751
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.
0
 
LVL 57
ID: 38743768
This BTW:

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)

  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.
0
 

Author Comment

by:Karen Schaefer
ID: 38744469
Please resend the correct attachment, Thanks for all you efforts.

Karen
0
 
LVL 57
ID: 38744492
Here's a good one...it's Report1 in the attached zip.

Jim.
OrgChartSample-new.zip
0
 

Author Comment

by:Karen Schaefer
ID: 38744547
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
0
 
LVL 57
ID: 38744988
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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

21 Experts available now in Live!

Get 1:1 Help Now