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
3  11494 
"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
"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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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?
|
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
|
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
|
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
|
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...
| | |
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.
|
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.
|
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...
|
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?
|
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...
|
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,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
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...
| |
