Count

[Guest]

I have an Employees Table that lists names, phone, email, dept, etc.
Department 01700 has subdepartments such as 01701, 01702, etc; department
02000 has subdepartments such as 02001, 02002, etc. I would like to count
the number of employees in department 01700 (including its subdepartments)
for one total amount of 01700 employees; and do the same for all employees in
02000 (including its subdepartments) as one total amount, etc. for all 40
departments. I have tried several versions of counting left, counting right,
etc. but can't seem to get anything to combine the departments correctly.
How can I roll-up all subdepartments into a main department total? Thanx.

 

[Dirk Goldgar]

I have an Employees Table that lists names, phone, email, dept, etc.
Department 01700 has subdepartments such as 01701, 01702, etc;
department 02000 has subdepartments such as 02001, 02002, etc. I
would like to count the number of employees in department 01700
(including its subdepartments) for one total amount of 01700
employees; and do the same for all employees in 02000 (including its
subdepartments) as one total amount, etc. for all 40 departments. I
have tried several versions of counting left, counting right, etc.
but can't seem to get anything to combine the departments correctly.
How can I roll-up all subdepartments into a main department total?
Thanx.

So employees in subdepartment 01701 have that listed in their department
field? That's a bit of a design flaw, since the department field is
doing double duty, storing both department -- 017, I guess -- and
subdepartment.

If the department is a text field, you should be able to group on the
leftmost three characters of the field, like this:

SELECT
Left([Dept], 3) As Department,
Count(*) As EmployeeCount
FROM
Employees
GROUP BY
Left([Dept], 3)

If department is number field (in which case the leading zeros you
posted aren't really meaningful), then you could group them by using
integer division to drop the last two digits:

SELECT
[Dept] \ 100 As Department,
Count(*) As EmployeeCount
FROM
Employees
GROUP BY
[Dept] \ 100

Note the use of the "\" operator instead of the "/" operator.