Missing Row Field in Crosstab Query

  • Thread starter Thread starter Bart
  • Start date Start date
B

Bart

I have a cross tab query below:

Owners Group Total 1 Month 2 Months
Support A 2 2
Support B 1 1

I have 3 Owners Group namely "Support A", "Support B" and "Support C".
However "Support C" does not appear because there is no value for "1 Month"
and
"1 Months" field. But I want to show all "Owners Group" even there is no
value for "Support C". I want my crosstab query to looks like below

Owners Group Total 1 Month 2 Months
Support A 2 2
Support B 1 1
Support C 0 0 0

Here is the SQL:
TRANSFORM Val(Nz(Count(Computation.[Ref No]),0)) AS [CountOfRef No]
SELECT Computation.[Owners Group], Count(Computation.[Ref No]) AS [Total Of
Ref No]
FROM Computation
GROUP BY Computation.[Owners Group]
PIVOT Computation.NoOfMonth In ("1 Month","2 Months");
 
If Computation is a query which includes an Owners Groups table (i.e. a table
of all groups with one row per owner group) the use an outer join so that all
groups are returned regardless of whether there is a match in the query's
other table(s).

If Computation is a table then add the Owners Groups table to the crosstab
query, again using an outer join on the Owners Group columns, so all groups
are returned.

Ken Sheridan
Stafford, England
 
Back
Top