Counting in a Query

[JeffH]

I trying to calculate the number of groups with 2 to 3 employees, the number
of groups with 4 to 5 employees, the number of groups with 6 to 24 employees
and the number of groups with 25 or more employees. I would like to do this
in one query rather than build a query to count each group. How can I
accomplish this?

 

[Tom van Stiphout]

On Mon, 4 Feb 2008 13:05:01 -0800, JeffH

Air code follows.
I would first build a query (named Query1) to get the number of
employees in each group. Something like:
select count(employeeID) as CountInGroup from Employees group by
GroupID

Then use DCount to count them:
select DCount("CountInGroup", "Query1", "CountInGroup between 2 and
3") as Group2-3,
DCount("CountInGroup", "Query1", "CountInGroup between 4 and 5") as
Group4-5,
DCount("CountInGroup", "Query1", "CountInGroup between 6 and 24") as
Group6-24,
DCount("CountInGroup", "Query1", "CountInGroup > 24") as GroupOver24,

-Tom.