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.
