Distinct Count in Crosstab

  • Thread starter Thread starter Annette
  • Start date Start date
A

Annette

I have an inspection table that has the following fields:
InspectionID, Type, Group, Question#, Status
I want a crosstab query that will list Type, Group and count the # of
questions for each status.

For example
Inspection ID Type Group Question Status
12 Comp CDC 1 In
12 Comp CDC 2 Out
12 Comp CDC 3 In
13 Comp CDC 1 In
13 Comp CDC 2 In
13 Comp CDC 3 In
12 Comp PTL 4 In
13 Comp PTL 4 Out


I would like the crosstab to return the following
Type Group Total Inspections In Out
Comp CDC 2 5 1
Comp PTL 2 1 1

Instead I am getting
Type Group Total Inspections In Out
Comp CDC 6 5 1
Comp PTL 2 1 1

In other words, the Total Inspection count needs to be the the count
of the distinct Inspection ID's and not the number of entries.
 
m:
I have an inspection table that has the following fields:
InspectionID, Type, Group, Question#, Status
I want a crosstab query that will list Type, Group and count the #
of questions for each status.

For example
Inspection ID Type Group Question Status
12 Comp CDC 1 In
12 Comp CDC 2 Out
12 Comp CDC 3 In
13 Comp CDC 1 In
13 Comp CDC 2 In
13 Comp CDC 3 In
12 Comp PTL 4 In
13 Comp PTL 4 Out


I would like the crosstab to return the following
Type Group Total Inspections In Out
Comp CDC 2 5 1
Comp PTL 2 1 1

Instead I am getting
Type Group Total Inspections In Out
Comp CDC 6 5 1
Comp PTL 2 1 1

In other words, the Total Inspection count needs to be the the
count of the distinct Inspection ID's and not the number of
entries.
Click to expand...

You need to break down the process into a few steps.
I'll show the SQL, but you can build your queries in the designer.

The first step is a simple query
Select Distinct [Inspection ID] from [TheTable];

the second step is to count the values in a Totals query
Select [Inspection ID], count([Inspection ID]) as [Total
Inspections] from [The First Query] Group By [Inspection ID];

The third step is to build a Crosstab query using the Table and the
second query.joined on the Inspection ID

TRANSFORM count([TheTable].[status]) as [whatever]
SELECT [TheTable].Type, [TheTable].Group,
[Second Query].[Total Inspections]
FROM [TheTable] Inner Join [Second Query]
ON [TheTable].[Inspection ID] =[Second Query].[Inspection ID]
GROUP BY [TheTable].Type, [TheTable].Group, [Second Query].[Total
Inspections]
PIVOT [TheTable].[Status];
 
Back
Top