Crosstab Query Null Values

[Guest]

When I do a cross tab query it gives me a null if a record does not exist.

How do I change the Null to 0

Thanks in advance

Trevor

 

[Duane Hookom]

Check the posting from about 5 hours earlier with a subject "How do I
replace null values in crosstab queries?"

You might want to do a quick search of posts prior to submitting your
question. It is usually much quicker.

 

[Allen Browne]

Switch the crosstab from design to SQL View (View menu).

Add Nz() around the expression in the first line, e.g. change:
TRANSFORM Sum(tblInvoiceDetail.Quantity) AS SumOfQuantity
to:
TRANSFORM Nz(Sum(tblInvoiceDetail.Quantity), 0) AS SumOfQuantity

 

[Andibevan]

I have the same problem but also need to know how to get rid of an extra
column that has a "<>" at the top of it.

Thanks

 

[Allen Browne]

The <> column represents null values.
You can remove them from the query by specifing:
Is Not Null
in the Criteria row under the Column Heading field.

 

[Andibevan]

Unfortunately, due to the nature of my query I can't access the design view
to try what you suggested.

I have therefore added "WHERE [Queryfour.Severity/Closure] IS NOT NULL" but
this means that the first row is not displayed when there are no records
present.

Any ideas?


TRANSFORM NZ(Count(Queryfour.DefectID),0) AS CountOfDefectID
SELECT Intervals.Descr AS Range, Count(Queryfour.DefectID) AS Total
FROM Queryfour RIGHT JOIN Intervals ON (Queryfour.VarAge <= Intervals.High)
AND (Queryfour.VarAge >= Intervals.Low)
WHERE [Queryfour.Severity/Closure] IS NOT NULL
GROUP BY Intervals.Descr
PIVOT [Queryfour.Severity/Closure];

 

[Allen Browne]

The other option would be to add an IN to the Pivot clause, and list the
valid values, e.g.:
PIVOT [Queryfour.Severity/Closure] In (1,2,3,4);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately, due to the nature of my query I can't access the design
view
to try what you suggested.

I have therefore added "WHERE [Queryfour.Severity/Closure] IS NOT NULL"
but
this means that the first row is not displayed when there are no records
present.

Any ideas?


TRANSFORM NZ(Count(Queryfour.DefectID),0) AS CountOfDefectID
SELECT Intervals.Descr AS Range, Count(Queryfour.DefectID) AS Total
FROM Queryfour RIGHT JOIN Intervals ON (Queryfour.VarAge <=
Intervals.High)
AND (Queryfour.VarAge >= Intervals.Low)
WHERE [Queryfour.Severity/Closure] IS NOT NULL
GROUP BY Intervals.Descr
PIVOT [Queryfour.Severity/Closure];

The <> column represents null values.
You can remove them from the query by specifing:
Is Not Null
in the Criteria row under the Column Heading field.

 

[Andibevan]

Thankyou Soooo much - that's fantastic - works a dream.

The other option would be to add an IN to the Pivot clause, and list the
valid values, e.g.:
PIVOT [Queryfour.Severity/Closure] In (1,2,3,4);

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately, due to the nature of my query I can't access the design
view
to try what you suggested.

I have therefore added "WHERE [Queryfour.Severity/Closure] IS NOT NULL"
but
this means that the first row is not displayed when there are no records
present.

Any ideas?


TRANSFORM NZ(Count(Queryfour.DefectID),0) AS CountOfDefectID
SELECT Intervals.Descr AS Range, Count(Queryfour.DefectID) AS Total
FROM Queryfour RIGHT JOIN Intervals ON (Queryfour.VarAge <=
Intervals.High)
AND (Queryfour.VarAge >= Intervals.Low)
WHERE [Queryfour.Severity/Closure] IS NOT NULL
GROUP BY Intervals.Descr
PIVOT [Queryfour.Severity/Closure];

The <> column represents null values.
You can remove them from the query by specifing:
Is Not Null
in the Criteria row under the Column Heading field.

I have the same problem but also need to know how to get rid of an extra
column that has a "<>" at the top of it.