Employee count with multiple parameters...

[rarmont]

I'm trying to create a query that counts the number of employees we have in
certain positions grouped by location and department. I have three locations
and two departments and I need to group the counts by position type. In
other words, I have three classifications for each position: Class A, Class
B, and Class C (Assistants, Operators, and Supervisors). I need a query that
will count the number of Assistants, Operators, and Supervisors without
regard to class. My job titles are Class A Assisant, Class B Assistant,
Class C Assistant, with the titles repeated for Operators and Supervisors. I
have the following SQL but it gives me the number of employees in each class
and I need to broaden it to list the number of employees currently in the
position type (Supervisor, Operator, Assistant):

TRANSFORM Count(Employee_Main.Emp_ID) AS CountOfEmp_ID
SELECT Employee_Main.Location, Employee_Main.Department,
Count(Employee_Main.Emp_ID) AS [Total Of Emp_ID]
FROM Employee_Main
GROUP BY Employee_Main.Location, Employee_Main.Department
PIVOT Employee_Main.Position;

Any ideas? I suppose I could add a position type to my records but it would
involve a lot of data entry once it's done since we have hundreds of
employees.

 

[Michel Walsh]

Not really sure I understand, but can you give a try to:


TRANSFORM ...
PIVOT Mid(Employee_main.Position, 8)




which, basically, strip the Class information from the description held in
field Position (as I understand the problem).




Vanderghast, Access MVP