Annette <(E-Mail Removed)> wrote in
news:ebfce22c-80fb-4178-b411-(E-Mail Removed)
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.
>
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];
--
Bob Q.
PA is y I've altered my address.
|
Similar Threads
