Count how many records meet criteria

  • Thread starter Thread starter jhicsupt via AccessMonster.com
  • Start date Start date
J

jhicsupt via AccessMonster.com

I want to count how many customers that meet a certain criteria.

I want to have three columns:

Field Name [NoofEes]
0-500 employees (this would include if null also)
501-5000 employees
Over 5000 employees

So if there are 75 customers with 450 employees, then in the field for 0-500
would be 75.

Any assistance would be appreciated.

Thanks.
 
One approach is to break the solution into three parts: create the
groupings, create the grouping fields (via a crosstab), and finally
generating the totals. A simple sample is shown below for these three
queries.

Create the groupings (Query named: CreateGroupings):

SELECT Employee.Company, Nz([NoOfEes],0) AS Employees, IIf(IsNull([NoofEes])
Or [NoOfEes]<=500,"0-500",IIf([NoOfEes]<=5000,"501-5000","Over 5000")) AS
[Group]
FROM Employee;

Create the grouping fields (Query named: EmployeesCrosstab):

TRANSFORM Sum(CreateGroupings.Employees) AS SumOfEmployees
SELECT CreateGroupings.Company
FROM CreateGroupings
GROUP BY CreateGroupings.Company
PIVOT CreateGroupings.Group;

Generate the totals:

SELECT "Totals" AS Expr1, Count(EmployeesCrosstab.[0-500]) AS
[CountOf0-500], Count(EmployeesCrosstab.[501-5000]) AS [CountOf501-5000],
Count(EmployeesCrosstab.[Over 5000]) AS [CountOfOver 5000]
FROM EmployeesCrosstab
GROUP BY "Totals";

I included Null values in the NoOfEes field in the 0-500 group. This may or
may not be an issue, and may or may not be how you want to treat Null values
for this field.


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I want to count how many customers that meet a certain criteria.

I want to have three columns:

Field Name [NoofEes]
0-500 employees (this would include if null also)
501-5000 employees
Over 5000 employees

So if there are 75 customers with 450 employees, then in the field for 0-500
would be 75.

Any assistance would be appreciated.

Thanks.
 
Back
Top