Re: Nz problem
- From: TuffyE <TuffyE@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 3 Nov 2006 06:01:02 -0800
As noted, I first tried Allen's solution and it seems to work fine. I will
try this, too, but it makes better sense to me to set the output type after
the calculation. On the other hand, I can understand performing the
calculation on data that has already been "set". Thank you for the idea.
No, there are no numerical fields in the data (part in question, anyway) and
I did intend to COUNT to develop the crosstab query data. Once it is being
analyzed in Excel, it is often necessary to SUM rows and columns of those
COUNTS and those were the steps being taken.
Tuffy
"MGFoster" wrote:
-----BEGIN PGP SIGNED MESSAGE-----.
Hash: SHA1
Put the Nz() function inside the COUNT() aggregate function:
TRANSFORM COUNT(Nz(RECV.BAND,0)) As CountOfBand
Actually, my guess is you want to sum not count. Count() will count
zero as 1 - COUNT(0) = 1. SUM(0) = 0.
TRANSFORM SUM(Nz(RECV.BAND,0)) As BandSum
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRUrF6YechKqOuFEgEQLkiwCcCNdb+XdJu6zHojiMjx2KkayKNbYAn1U8
GBbiZgIDVJo8K9CHPU6pqVVw
=q6pb
-----END PGP SIGNATURE-----
TuffyE wrote:
As the solution to "filling" a crosstab, it was suggested that I use this
function. The line is:
TRANSFORM Nz(Count(RECV.[BAND]),0) AS CountOfBAND
However, that generates some results that I just can't understand when I
analyze it with Excel. The data part of the output table appears to have
values, some of which are "0" and some are not. The first thing that I
noticed was that the Excel SUM function would always come up with a result of
0, whether or not there appear to be any other values or not. Hmmm, sounds
like a Text problem with the 0 filling. So, I tried a SUM with only the
values and that still yielded 0. Just for the heck of it, I tried a simple
cell addition (eg. =G2+G4) and that does add (18 in example below). What
does all of that mean? I didn't think there was that kind of difference
between "=SUM(G2,G4)" and "=G2+G4". Does the Nz cause it to output text
rather than values as the count? Is there a values version that will yield
"0" where that is the COUNT result?
000 1320 2059 0 0 1 9
000 1326 13 0 0 0 0
000 1330 346 0 0 3 9
000 1340 13 0 0 0 0
000 1350 24 0 0 0 0
000 1370 35 0 0 0 0
000 1390 53 0 0 0 0
000 1400 143 0 0 0 0
000 1410 4 0 0 0 0
2690 0 0 0 0
0
18
- References:
- Re: Nz problem
- From: MGFoster
- Re: Nz problem
- Prev by Date: Re: Creating query without using Wizard
- Next by Date: Re: calculate hours correctly in Access-query
- Previous by thread: Re: Nz problem
- Next by thread: Top 50 Records for each category
- Index(es):
Relevant Pages
|
