Q: Null values and NZ() in crosstab query

John

I've read several prior posts in this group about using nz() to
convert null values to zero; however, I'm not sure how/where to
implement this function in my crosstab query.

The crosstab query (qryPromoFilm_NetCM_Crosstab) uses another query
(qryPromo_NetCM) as its source. The crosstab is used to show revenue
spread out through the twelve months. I need the months with null
values to have a "0" value.

This is the SQL for the crosstab query (qryPromoFilm_NetCM_Crosstab):
TRANSFORM Avg(qryPromo_NetCM.curNetCM) AS AvgOfcurNetCM
SELECT qryPromo_NetCM.strPromoTitle, qryPromo_NetCM.strTitle
FROM qryPromo_NetCM
GROUP BY qryPromo_NetCM.strPromoTitle, qryPromo_NetCM.strTitle
PIVOT qryPromo_NetCM.lngFiscalMonthID In (1,2,3,4,5,6,7,8,9,10,11,12);

This is the SQL for the crosstab query's source (qryPromo_NetCM):
SELECT tblPromos.strPromoTitle, tblFilms.strTitle,
tblPromo_NetCM.lngFiscalYear, tblFiscalMonths.strFiscalMonth,
tblPromo_NetCM.curNetCM, tblPromo_NetCM.lngFiscalMonthID,
tblPromo_Films.lngPromoFilmID
FROM tblPromos INNER JOIN ((tblFilms INNER JOIN tblPromo_Films ON
tblFilms.lngFilmID = tblPromo_Films.lngFilmID) INNER JOIN
(tblFiscalMonths INNER JOIN tblPromo_NetCM ON
tblFiscalMonths.lngFiscalMonthID = tblPromo_NetCM.lngFiscalMonthID) ON
tblPromo_Films.lngPromoFilmID = tblPromo_NetCM.lngPromoFilmID) ON
tblPromos.lngPromoID = tblPromo_Films.lngPromoID
ORDER BY tblPromos.strPromoTitle, tblFilms.strTitle,
tblPromo_NetCM.lngFiscalYear, tblPromo_NetCM.lngFiscalMonthID;
Any suggestions on where/how to implement the nz() function?

TIA,
John

Nov 12 '05 #1

DFS

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've read several prior posts in this group about using nz() to
convert null values to zero; however, I'm not sure how/where to
implement this function in my crosstab query.

Most likely in the TRANSFORM line:

TRANSFORM Avg(nz(qryPromo_NetCM.curNetCM)) AS AvgOfcurNetCM

I haven't tried nz() in a crosstab, but it may work. If not, I believe this
will:

TRANSFORM iif(isnull(qryPromo_NetCM.curNetCM),0,
avg(qryPromo_NetCM.curNetCM)) AS AvgOfcurNetCM

The crosstab query (qryPromoFilm_NetCM_Crosstab) uses another query
(qryPromo_NetCM) as its source. The crosstab is used to show revenue
spread out through the twelve months. I need the months with null
values to have a "0" value.

This is the SQL for the crosstab query (qryPromoFilm_NetCM_Crosstab):
TRANSFORM Avg(qryPromo_NetCM.curNetCM) AS AvgOfcurNetCM
SELECT qryPromo_NetCM.strPromoTitle, qryPromo_NetCM.strTitle
FROM qryPromo_NetCM
GROUP BY qryPromo_NetCM.strPromoTitle, qryPromo_NetCM.strTitle
PIVOT qryPromo_NetCM.lngFiscalMonthID In (1,2,3,4,5,6,7,8,9,10,11,12);

This is the SQL for the crosstab query's source (qryPromo_NetCM):
SELECT tblPromos.strPromoTitle, tblFilms.strTitle,
tblPromo_NetCM.lngFiscalYear, tblFiscalMonths.strFiscalMonth,
tblPromo_NetCM.curNetCM, tblPromo_NetCM.lngFiscalMonthID,
tblPromo_Films.lngPromoFilmID
FROM tblPromos INNER JOIN ((tblFilms INNER JOIN tblPromo_Films ON
tblFilms.lngFilmID = tblPromo_Films.lngFilmID) INNER JOIN
(tblFiscalMonths INNER JOIN tblPromo_NetCM ON
tblFiscalMonths.lngFiscalMonthID = tblPromo_NetCM.lngFiscalMonthID) ON
tblPromo_Films.lngPromoFilmID = tblPromo_NetCM.lngPromoFilmID) ON
tblPromos.lngPromoID = tblPromo_Films.lngPromoID
ORDER BY tblPromos.strPromoTitle, tblFilms.strTitle,
tblPromo_NetCM.lngFiscalYear, tblPromo_NetCM.lngFiscalMonthID;
Any suggestions on where/how to implement the nz() function?

TIA,
John

Nov 12 '05 #2

Rick Brandt

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've read several prior posts in this group about using nz() to
convert null values to zero; however, I'm not sure how/where to
implement this function in my crosstab query.

I'm not real sure what it would look like in the SQL, but essentially if
you look at the Crosstab in the design grid the column being aggregated
will typically have "Sum" or "Count" in the Total Row and the name of the
field being aggregated in the Field row. You change the Total row to
"Expression" and then change the Field row to "Nz(Sum(FieldName),0)" or
Nz(Count(FieldName),0). Then you get zeros in the output instead of Nulls.
I usually throw a "+0" onto end of the expression to force a numeric
output. Otherwise the Nz() will change the output to a string.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #3

John

Rick, your suggestion worked just fine...thanks a lot for your help!

-John

"Rick Brandt" <ri*********@hotmail.com> wrote in message news:<bq*************@ID-98015.news.uni-berlin.de>...

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

I've read several prior posts in this group about using nz() to
convert null values to zero; however, I'm not sure how/where to
implement this function in my crosstab query.

I'm not real sure what it would look like in the SQL, but essentially if
you look at the Crosstab in the design grid the column being aggregated
will typically have "Sum" or "Count" in the Total Row and the name of the
field being aggregated in the Field row. You change the Total row to
"Expression" and then change the Field row to "Nz(Sum(FieldName),0)" or
Nz(Count(FieldName),0). Then you get zeros in the output instead of Nulls.
I usually throw a "+0" onto end of the expression to force a numeric
output. Otherwise the Nz() will change the output to a string.

Nov 12 '05 #4