473,698 Members | 2,022 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_N etCM_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_N etCM_Crosstab):
TRANSFORM Avg(qryPromo_Ne tCM.curNetCM) AS AvgOfcurNetCM
SELECT qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
FROM qryPromo_NetCM
GROUP BY qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
PIVOT qryPromo_NetCM. lngFiscalMonthI D 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.strPr omoTitle, tblFilms.strTit le,
tblPromo_NetCM. lngFiscalYear, tblFiscalMonths .strFiscalMonth ,
tblPromo_NetCM. curNetCM, tblPromo_NetCM. lngFiscalMonthI D,
tblPromo_Films. lngPromoFilmID
FROM tblPromos INNER JOIN ((tblFilms INNER JOIN tblPromo_Films ON
tblFilms.lngFil mID = tblPromo_Films. lngFilmID) INNER JOIN
(tblFiscalMonth s INNER JOIN tblPromo_NetCM ON
tblFiscalMonths .lngFiscalMonth ID = tblPromo_NetCM. lngFiscalMonthI D) ON
tblPromo_Films. lngPromoFilmID = tblPromo_NetCM. lngPromoFilmID) ON
tblPromos.lngPr omoID = tblPromo_Films. lngPromoID
ORDER BY tblPromos.strPr omoTitle, tblFilms.strTit le,
tblPromo_NetCM. lngFiscalYear, tblPromo_NetCM. lngFiscalMonthI D;
Any suggestions on where/how to implement the nz() function?

TIA,
John
Nov 12 '05 #1
3 11489
DFS

"John" <so*********@ho tmail.com> wrote in message
news:90******** *************** ***@posting.goo gle.com...
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(qryP romo_NetCM.curN etCM),0,
avg(qryPromo_Ne tCM.curNetCM)) AS AvgOfcurNetCM

The crosstab query (qryPromoFilm_N etCM_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_N etCM_Crosstab):
TRANSFORM Avg(qryPromo_Ne tCM.curNetCM) AS AvgOfcurNetCM
SELECT qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
FROM qryPromo_NetCM
GROUP BY qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
PIVOT qryPromo_NetCM. lngFiscalMonthI D 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.strPr omoTitle, tblFilms.strTit le,
tblPromo_NetCM. lngFiscalYear, tblFiscalMonths .strFiscalMonth ,
tblPromo_NetCM. curNetCM, tblPromo_NetCM. lngFiscalMonthI D,
tblPromo_Films. lngPromoFilmID
FROM tblPromos INNER JOIN ((tblFilms INNER JOIN tblPromo_Films ON
tblFilms.lngFil mID = tblPromo_Films. lngFilmID) INNER JOIN
(tblFiscalMonth s INNER JOIN tblPromo_NetCM ON
tblFiscalMonths .lngFiscalMonth ID = tblPromo_NetCM. lngFiscalMonthI D) ON
tblPromo_Films. lngPromoFilmID = tblPromo_NetCM. lngPromoFilmID) ON
tblPromos.lngPr omoID = tblPromo_Films. lngPromoID
ORDER BY tblPromos.strPr omoTitle, tblFilms.strTit le,
tblPromo_NetCM. lngFiscalYear, tblPromo_NetCM. lngFiscalMonthI D;
Any suggestions on where/how to implement the nz() function?

TIA,
John

Nov 12 '05 #2
"John" <so*********@ho tmail.com> wrote in message
news:90******** *************** ***@posting.goo gle.com...
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(FieldNa me),0)" or
Nz(Count(FieldN ame),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*********@ho tmail.com> wrote in message news:<bq******* ******@ID-98015.news.uni-berlin.de>...
"John" <so*********@ho tmail.com> wrote in message
news:90******** *************** ***@posting.goo gle.com...
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(FieldNa me),0)" or
Nz(Count(FieldN ame),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
8736
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 be a match). Access won't return these records. If the fields are filled with actual values, everything is fine. Can somebody help me on this one?
1
3339
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 that should be 0 are blank. I trid the old IIF(IsNull(....etc but that didn't help. How do I make my blanks 0s? Thanks. Matthew Wells MWells@NumberCruncher.com
3
4011
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 query will not work unless there are values in the data i am attempting to send. I want the fields in the destination table to accept null and populated values. Any help here would be gladly accepted. thanks
12
6643
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 access to show NULL and 0 values seperately. I've tried the following Format properties in my text box: 0.0;;0;"" --> Both NULL and 0 values display as 0 0.0;;"";"" --> Both NULL and 0 values show up blank
4
11961
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? Here is the code: TRANSFORM Val(nz(Count(Event),0)) AS CountOfEvent SELECT Format(,"yyyy-mm") AS MY FROM dbo_Table GROUP BY Format(,"yyyy-mm") PIVOT dbo_Table.Event; Thanks for any help that anyone can give, I'm stumped! Surely there is an...
4
2817
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 values of a text box in my form. thanks.
5
8319
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 easy way in which to automatically replace these null values with a zero? Many thanks.
3
12291
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 (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert...
10
9029
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 base my average on just 2 values instead of 3 i am currently using in my string. How can i have the form update the "3" based on the number of values that are populated?
0
8674
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9027
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8895
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6518
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4369
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3046
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2329
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2001
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.