Count of active employees per department

[kattatonic1]

I have Departments, and Employees in Departments. Employees can be deactivated for various reasons (leaves of different kinds, secondments, etc.) so there is a field strEmployeeStatus. If strEmployeeStatus = "A", she be active; if strEmployeeStatus = "I", she be inactive. I need a query that tells me how many active employees there is in each department. I am thinking create a field with a query? I don't want to list the employees. I just want a total for each department.



Any suggestions appreciated,

Kay
from Toronto

[orange]

Air code /untested

Select Department, strEmployeeStatus, Count(Employees)
From EmployeesInDepartment
Group by Department, strEmployeeStatus

[kattatonic1]

Thanks for your quick reply, Orange! I have realized that I asked the wrong question. Ugh.

I have a form for Departments with departmental data. On that form I simply want a field that is a calculated count of active employees for that department. I would use the criteria that all related employees with strEmployeeStatus = "A".

So I suppose I need a calCountofActiveEmployees field. I can't put it in the Departments table because it won't let me choose from a related table in the calculation.

I have designed a query that counts the active employess, but it won't let me add any non-totalable fields to the query.

Am I missing something simple?

Kay
in Toronto

[orange]

Look at this link and see if you can use a DCount().

What is the current recordSource of your Form?

Can you zip a copy of your database -- remove anything confidential/private.
Just need enough records to show the issue.