Graphic-How have ZERO if count is null? 1

[Guest_imported]

In a graphic I have a crosstab query to count "recom" grouping by DESC_COD
If there is no recom in one year in graphic I dont'have this year.How can I have 0 (ZERO) in this case?

The query is:
TRANSFORM Count(grafico_recomendacoes.recom) AS CountOfrecom
SELECT (Year([DT_ABERTURA])) AS anos
FROM grafico_recomendacoes
WHERE (((grafico_recomendacoes.recom) Is Not Null))
GROUP BY (Year([DT_ABERTURA]))
ORDER BY (Year([DT_ABERTURA]))
PIVOT grafico_recomendacoes.DESC_COD;

Thanks a lot for your help
garrido

 

[gwinn7]

If you are concerned about a NULL value, then you can use the "Nz" function. This function can be used to strip a Null out of a value and return whatever value you specify to return when a Null is found. Example:

Return = Nz(Variable, 0)

If Variable is NULL, then the "Return" will be "0" and NOT null. If the Variable contains a value other than NULL, then the original value will be preserved.

You can also apply this in a query...

"SELECT nz(Column,0) as Column1 FROM TableA;"

Does this help?

Gary
gwinn7

 

[Guest_imported]

Sorry,but it don´t work
I'll try to explain my problem:
I have a crosstab query like this:
Years Code num_rec
Group by group by count
rowheading columnHeading

the problem is:For some year if I have no "num_rec" the "code"(columnHeading)is empty and the graphic will be with one year less.
Is that clear?
thanks
garrido

 

[Craig0201]

I think Garrido is after something that with data

1995
1995
1995
1997
1997
1998
1999
1999
2000
2001

would return the query

1995 3
1996 0
1997 2
1998 1
1999 2
2000 1
2001 1

not

1995 3
1997 2
1998 1
1999 2
2000 1
2001 1

Am I right? If so, this is something i've been struggling with and not getting anywhere!! So i'd appreciate the help too!!

Craig

 

[LarryDeLaruelle]

Unfortunately, the Nz function will not replace Nulls with zeros in a cross tab (I've been trying to find a way but have yet to find a solution for this).

You can force column headers to appear when a column would contain nulls.

In the query click on the column where the column headers are defined, right click and select properties. You'll see a General Tab with a property called Column Headings.

Type the headings you want, which must match the values in the table, separated by commas.

Hope this helps.
Larry De Laruelle
larry1de@yahoo.com

 

[tatarrila]

It´s a problem!
To Larry De Laruelle:
it works if there is any value in ALL column heading for some row heading.
In my case, in one Row heading (year 2001), I have one value just for one column heading.The others columns headings for year 2001 are empty.
So in my graphic for years 1999,2000 I have four columns(columns headings), for year 2001 I have just 3 columns.

Do you think there is nothing to do about this?

 

[ghubbell]

Take one minute to go into your source table, sort your data ascending, do a find/replace null values with what ever it is you are missing (a number, a date, whatever.) Now set some default value for that field for the future. Gord
ghubbell@total.net

 

[tatarrila]

to ghubbel:
I can´t do what you sugest.
in this case - crosstab query - I have no record for some columns headings:
column heading1 column heading2
2000 20 30
2001 2 (empty)

Is clear the problem?
Garrido
togarrido@hotmail.com

 

[tatarrila]

to ghubbel:
I can´t do what you sugest.
in this case - crosstab query - I have no record for some columns headings:
column heading1 column heading2
2000 20 30
2001 2 (empty)

Is clear the problem?

To Craig0201 (is your problem too?)
Garrido
togarrido@hotmail.com

 

[ghubbell]

Alright: Split your crosstab query: first run up your data and Nz any columns that must be filled. Now run a crosstab on the first query. I think this will work. Gord
ghubbell@total.net

 

[tatarrila]

To Ghubbell
As LarryDeLaruelle says nz don't replace nulls with zeros.
unfortunally I must stop-I can't do what I want.
Thanks every body anyway
Garrido

 

[ghubbell]

re: nz don't replace nulls with zeros. Perhaps not in a crosstab but easily in a query on its own. Don't give up!
Gord
ghubbell@total.net

 

[tatarrila]

I don't like to give up but sometimes....
as you see in the example I give and in the example of Craig0201 the problem is I have no records for some columns headings!For 2001 I have I have x records in column heading1 and none for column heading2.
Any way I must keep trying
Thanks a lot.
tatarrila