help with a Counter on a Report

  • Thread starter Thread starter grace
  • Start date Start date
G

grace

I have a counter on a report that counts the number of
people on a list sorted by job code:

Attorneys
1. Person1
2. Person2
3. Person3
.....

Paralegals
1. Person1
2. Person2
.....

I have field [BudgetHeadCount] that is available in the
report that lists the number of budgeted attorneys,
paralegals, etc. I would like the counter to keep
counting based on the [BudgetHeadCount] figure. So, in
the above example, if I have 5 budgeted attorneys and 3
budgeted paralegals, I would want the counter to display
the following:

Attorneys
1. Person1
2. Person2
3. Person3
4.
5.

Paralegals
1. Person1
2. Person2
3.

The Counter is currently set to =IIf([EmployeeStatus]
="Terminated",Null,[CounterTextBox]) and the
CounterTextBox field, which is not visible, is set to: =IIf
([EmployeeStatus]="Terminated",0,1). This is so that any
terminated employees will not show up with a number. This
works fine and I would like to continue using this feature.

What would I set the control source on the counter to in
order to get the counter to equal to the [BudgetHeadCount]
for the specific jobcode?

Your help is greatly appreciated.
 
Here's how I would do this.
1) create a table of numbers tblNums with a single numeric field [Num] and
records with values from 1 to the maximum number of employees you will ever
have in any job code.
2) create or use a table that has each job code with the [BudgetHeadCount].
3) add both the tables into a query and add the [JobCode] and [Num] fields
to the grid.
4) under the [Num] column place a criteria
<=[BudgetHeadCount]
5) save your query as qcarBudHead ;-)
6) create a query with your persons in it with required fields and add a
calculated column that numbers the employees within each JobCode. There have
been many posting regarding ranking queries. Your expression might be:
Num: DCount("EmpID","tblEmployees","EmpID <=" & [EmpID] & " AND
[EmployeeStatus]<>'Terminated' AND [JobCode]=""" & [JobCode] & """")
7) save this query as qselEmpJobRank
8) create a new query based on qcarBudHead and qselEmpJobRank
9) join the JobCode and Num fields and edit the join properties to include
"All the records from qcarBudHead ..."
10) use this new query as the base of your report.
 
Back
Top