Prev: Query Results Where Qty Shipped is not Divisable by 4
Next: Can I do this in a query?
From: HelpMe! on 19 Feb 2010 14:47
I have a EmployeeInfo table with 1000 records. Each employee has an
autonumber EmployeeID field. Within that same table there are 117
supervisors, identified in the SupName field. I would like to summarize the
count of EmployeeID by SupName. I have no idea where to begin... Please let
me know if you need more info.
Thanks all!
From: Jerry Whittle on 19 Feb 2010 15:05
SELECT SupName, Count(EmployeeID)
FROM EmployeeInfo
GROUP BY SupName
ORDER BY SupName ;

Open a new query but don't add any tables. Go to View, SQL View and past in
the above.

As a side note: Assuming that the Supervisor is in the EmployeeInfo table as
an employee, you shouldn't have a name in the SupName field. Instead you
should have the EmployeeID of the supervisor there. Then you could do a self
join on the table. Why? Here's just one example: A Supervisor changes their
name, like getting married or something. With your setup you would need to
change the supervisor's name in their employee record AND for each employee
where they are the supervisor. If you did it by EmployeeID in the SupName (or
better yet SupID) field, you would only need to change it in one place.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"HelpMe!" wrote:

> I have a EmployeeInfo table with 1000 records. Each employee has an
> autonumber EmployeeID field. Within that same table there are 117
> supervisors, identified in the SupName field. I would like to summarize the
> count of EmployeeID by SupName. I have no idea where to begin... Please let
> me know if you need more info.
> Thanks all!
 | 
Pages: 1
Prev: Query Results Where Qty Shipped is not Divisable by 4
Next: Can I do this in a query?