Missing Row Field in Crosstab Query

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");
 
K

Ken Sheridan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top