473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Q: Null values and NZ() in crosstab query

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
3 11445
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
"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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Larry Peeters | last post by:
Hi, I have created a very simple query to link two tables on 4 fields. However, in certain cases, one of the fields used to link the table may contain nulls (in both tables, so this should still...
1
by: Matthew Wells | last post by:
I have a crosstab query based on anothe query. The base query resultset has no null values in its "Quantity" column. However, when I create the new crosstab query from the base query, the records...
3
by: JOEP | last post by:
What do I need to do to allow an append query to post null values to records in a field of the destination table? Basically I want to allow records with null values to post to the table. The append...
12
by: jkearns | last post by:
Hello, I made a report from a crosstab query following the steps onlined in MSDN's Solutions.mdb example. I now have a dynamic crosstab report (great!), but with one minor problem. I cannot get...
4
by: jeanlee | last post by:
I have a crosstab query of events by month, and many months do not have any events. How can I make the crosstab show the months (crosstab rows) that don't have any events? Is there a shortcut? ...
4
by: greg | last post by:
Hi, I don't think my message posted correctly so here it is. Is there any way to access the individual values of a form text box? I want to iterate through all of the rows and access the...
5
by: Nobby | last post by:
On a crosstab query, I often produce reports that show Counts of values. the problem that I have is that where there are no counts, no values are returned i.e. there are blank cells. Is there an...
3
ADezii
by: ADezii | last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero...
10
by: Toby Gallier | last post by:
Hello! I have a form that is calculating averages as follows: " =(NZ()+Nz()+Nz())/3 " However I need to now adjust for null values , so for example if value2 is null I would then need to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.