Counting Awol, Terminated, On-Leave Employees in Status Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RAM - 04 Jan 2006 01:07 GMT

Hi, guys! Good morning! I am using access 2003 and writing a simple database
for employees' 201 file. I want to print the total number of employees who
went on awol, terminated and currently active at the end of the report. I
have a column named "Status" and the values are as follows:

1-active
2-sick leave
3-maternity leave
4-awol
5-resigned
6-terminated

I was able to count the total number of employees, both active, awol, and
on-leave using the following at the Control Source:

="Total Number of Employees: (" & Count(*) & " " & IIf(Count(*)=1,"","") & ")"

but I can't get the total number of employees on-leave, awol, and separated
from the company. Please help. Thank you very much.

Signature

Thank you very much, guys!

RAM

Reply to this Message

Duane Hookom - 04 Jan 2006 02:38 GMT

awol employees in a text box in the Report Footer (or any group header or
footer):
="Total Number of awol Employees: (" & Abs(Sum([Status]="awol")) & ")"
You should be able to figure the remainder.

I would probably use a totals subreport in the report footer. This would
automatically add other stati when they get added.

Signature

Duane Hookom
MS Access MVP
--

> Hi, guys! Good morning! I am using access 2003 and writing a simple
> database
[quoted text clipped - 18 lines]
> separated
> from the company. Please help. Thank you very much.

Reply to this Message

RAM - 04 Jan 2006 02:56 GMT

Thank you very much! It helped a lot!

Signature

Thank you very much, guys!

RAM

> awol employees in a text box in the Report Footer (or any group header or
> footer):
[quoted text clipped - 26 lines]
> > separated
> > from the company. Please help. Thank you very much.

Reply to this Message

RAM - 04 Jan 2006 06:11 GMT

Hi, Duane! Thanks for the help. Anyway, I have another problem. I have
"Years of Service" field and using

=DateDiff("yyyy",[DateHired],Now())+Int(Format(Now(),"mmdd")<Format([DateHired],"mmdd")) & " " & " Years"

it gives me the number of years the employee is with the company. But I
want to stop the counter the moment he/she leaves the company. I have another
field named "DateSeparated". How will I accomplish that? Thanks again.

Signature

Thank you very much, guys!

RAM

> awol employees in a text box in the Report Footer (or any group header or
> footer):
[quoted text clipped - 26 lines]
> > separated
> > from the company. Please help. Thank you very much.

Reply to this Message

Duane Hookom - 04 Jan 2006 13:13 GMT

Replace "Now()" in your expression with "Nz([DateSeparated],Date())".

Signature

Duane Hookom
MS Access MVP
--

> Hi, Duane! Thanks for the help. Anyway, I have another problem. I have
> "Years of Service" field and using
[quoted text clipped - 41 lines]
>> > separated
>> > from the company. Please help. Thank you very much.

Reply to this Message