473,557 Members | 2,835 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Suppress Zero values in a Cross Tab report

Hello, hope somebody here can help me... I have a query that lists
defects recorded in a user defined date range. That query is then used
as the source for a Cross Tab query that cross-tabs count of defect
type by calendar month. Defect types are stored in one table, defect
transactions in another along with date etc. When I cross-tab the
results, defect types that have no defects recorded against them
appear as a blank (null) value. That is, the zero value is suppressed.
Unfortunately, in the management reports that are produced, they (QA
Managers) want to see a Zero value, not a null value. (they postulate
that a null could be interpreted as "we forgot to count those
values...", NOT "we had no defects of that type..)

Is there a simple way to "un"-suppress zero values. I had thought of
doing an Immediate If, (that is, Iif count=Null, show "0", otherwise
show Count) but cannot get that to work...
I also thought of giving each transaction a notional value of "1",
then summing the results in a cross tab, but since a defect count is
null because we haven't recorded any transactions of that type, you
can't sum something that isn't there! A third option was to use the
immediate if in the "OnFormat" event of the report itself - still
can't make it work...

Any help would be hugely appreciated. Thanks in advance.

THE SQL statement for the current query is as follows:

TRANSFORM Count(QryCompla intSummary.Reco rdID) AS [The Value]
SELECT QryComplaintSum mary.DefectType Desc,
Count(QryCompla intSummary.Reco rdID) AS [Total Of RecordID]
FROM QryComplaintSum mary
GROUP BY QryComplaintSum mary.DefectType Desc
PIVOT Format([Complaint Date],"mmm") In
("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","Sep","Oct" ,"Nov","Dec" );
Nov 13 '05 #1
4 5563
Hello
Surprise, surprise: you have to do it other way! I usually have to
supress printing zeros...
If you can define transactions column in the table as number,
cross-tab query will calculate zeros as default, I think.
Galina
da********@optu snet.com.au (David Peach) wrote in message news:<ee******* *************** ****@posting.go ogle.com>...
Hello, hope somebody here can help me... I have a query that lists
defects recorded in a user defined date range. That query is then used
as the source for a Cross Tab query that cross-tabs count of defect
type by calendar month. Defect types are stored in one table, defect
transactions in another along with date etc. When I cross-tab the
results, defect types that have no defects recorded against them
appear as a blank (null) value. That is, the zero value is suppressed.
Unfortunately, in the management reports that are produced, they (QA
Managers) want to see a Zero value, not a null value. (they postulate
that a null could be interpreted as "we forgot to count those
values...", NOT "we had no defects of that type..)

Is there a simple way to "un"-suppress zero values. I had thought of
doing an Immediate If, (that is, Iif count=Null, show "0", otherwise
show Count) but cannot get that to work...
I also thought of giving each transaction a notional value of "1",
then summing the results in a cross tab, but since a defect count is
null because we haven't recorded any transactions of that type, you
can't sum something that isn't there! A third option was to use the
immediate if in the "OnFormat" event of the report itself - still
can't make it work...

Any help would be hugely appreciated. Thanks in advance.

THE SQL statement for the current query is as follows:

TRANSFORM Count(QryCompla intSummary.Reco rdID) AS [The Value]
SELECT QryComplaintSum mary.DefectType Desc,
Count(QryCompla intSummary.Reco rdID) AS [Total Of RecordID]
FROM QryComplaintSum mary
GROUP BY QryComplaintSum mary.DefectType Desc
PIVOT Format([Complaint Date],"mmm") In
("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","Sep","Oct" ,"Nov","Dec" );

Nov 13 '05 #2
On 8 Aug 2004 23:46:47 -0700, da********@optu snet.com.au (David Peach)
wrote:
Hello, hope somebody here can help me... I have a query that lists
defects recorded in a user defined date range. That query is then used
as the source for a Cross Tab query that cross-tabs count of defect
type by calendar month. Defect types are stored in one table, defect
transactions in another along with date etc. When I cross-tab the
results, defect types that have no defects recorded against them
appear as a blank (null) value. That is, the zero value is suppressed.
Unfortunatel y, in the management reports that are produced, they (QA
Managers) want to see a Zero value, not a null value.


Hi David

There may be some simple way, but I think you need to run the crosstab
on a query which already contains all the required table entries. For
example you can form a cross product (all pairs month/defect) by a
query "AllRequiredPai rs" such as

SELECT DefectTypes.Def ectType, MonthsTable.mnt h
FROM DefectTypes, MonthsTable;

(MonthsTable is just a list "Jan", "Feb", "Mar "etc)

You can then form a query "Group1" which is the union of the required
pairs with the actual defects which have occurred.

SELECT DefectOccurrenc es.DefectType, Format([Complaint Date],"mmm")
AS mnth, "actual defect"
FROM DefectOccurrenc es
UNION Select DefectType, mnth, "dummy entry" from
AllRequiredEntr ies;

(I have added the third unnamed column to the union to make sure that
all records are unique otherwise it won't work. These two queries
could be combined)

Finally the crosstab can be something like

TRANSFORM Count([DefectType])-1
SELECT group1.DefectTy pe FROM group1
GROUP BY group1.DefectTy pe
PIVOT group1.mnth In
("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","Sep","Oct" ,"Nov","Dec" );

where the minus one removes the "dummy" required items.

David

Nov 13 '05 #3
David, Thanks for your help, but there's an easier way - provided to
me by a genius in another Access forum...(its probably not polite to
say here which forum...!) Following is his code not mine....

=============== ==========
Replace

Count(QryCompla intSummary.Reco rdID)

with

Nz(Count(QryCom plaintSummary.R ecordID),0)

The Nz function returns its first argument but replacess null values
by the second argument. Usually, the result of Nz is text. If you need
numeric values for further calculations, use

CLng(Nz(Count(Q ryComplaintSumm ary.RecordID),0 ))

=============== ==============
I'd never used the Nz function before - very handy. This worked a
treat and is very simple & elegant.

Thanks for the follow -up though - much appreciated.

dp


d.************* **@blueyonder.c o.uk (David Schofield) wrote in message news:<4117b0ef. 745334354@local host>...
On 8 Aug 2004 23:46:47 -0700, da********@optu snet.com.au (David Peach)
wrote:
Hello, hope somebody here can help me... I have a query that lists
defects recorded in a user defined date range. That query is then used
as the source for a Cross Tab query that cross-tabs count of defect
type by calendar month. Defect types are stored in one table, defect
transactions in another along with date etc. When I cross-tab the
results, defect types that have no defects recorded against them
appear as a blank (null) value. That is, the zero value is suppressed.
Unfortunatel y, in the management reports that are produced, they (QA
Managers) want to see a Zero value, not a null value.


Hi David

There may be some simple way, but I think you need to run the crosstab
on a query which already contains all the required table entries. For
example you can form a cross product (all pairs month/defect) by a
query "AllRequiredPai rs" such as

SELECT DefectTypes.Def ectType, MonthsTable.mnt h
FROM DefectTypes, MonthsTable;

(MonthsTable is just a list "Jan", "Feb", "Mar "etc)

You can then form a query "Group1" which is the union of the required
pairs with the actual defects which have occurred.

SELECT DefectOccurrenc es.DefectType, Format([Complaint Date],"mmm")
AS mnth, "actual defect"
FROM DefectOccurrenc es
UNION Select DefectType, mnth, "dummy entry" from
AllRequiredEntr ies;

(I have added the third unnamed column to the union to make sure that
all records are unique otherwise it won't work. These two queries
could be combined)

Finally the crosstab can be something like

TRANSFORM Count([DefectType])-1
SELECT group1.DefectTy pe FROM group1
GROUP BY group1.DefectTy pe
PIVOT group1.mnth In
("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","Sep","Oct" ,"Nov","Dec" );

where the minus one removes the "dummy" required items.

David

Nov 13 '05 #4
>Replace

Count(QryCompl aintSummary.Rec ordID)

with

Nz(Count(QryCo mplaintSummary. RecordID),0)


How embarrassing. This is the first thing I tried but through years of
sloppy programming I used Nz without the second argument. I see in the
help file that the second argument is "Optional (unless used in a
query)"
So I have learned something as well!!

Nov 13 '05 #5

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

Similar topics

9
12026
by: Doug Ly | last post by:
Hi, When I run this query using WinSQL to connect to a DB2 database, it gave me the warning: Error: SQLSTATE 01003: Null values were eliminated from the argument of a column function. (State:01003, Native Code: 0) Is there anyway to suppress this message in my report? Thanks
2
5079
by: Tony | last post by:
Hi all, I have a table with numerical information in it (scientific notation) and a simple report that displays this information relevant to a query on the data. My question - Some of the data has a valid zero value. Is it possible to make the report NOT display the zero value and only the cells that contain a whole number?
5
7339
by: Randy Harris | last post by:
How can I report an average of non zero values? If the values are: 5, 0, 6, 0, 4 I would like the result 5 (15 / 3), not 3 (15 / 5) Thanks for any help...
1
6381
by: Bill Nguyen | last post by:
Report source is an SQLSERVER 2K store procedure. VB.NET application. Report created by CR 8.5. At runtime, I still had to click "CANCEL" to bypass the parameter prompts before the report display correctly. Also, print option grayed out although it was available at design time. Thanks a million! Bill
2
4918
by: pmarisole | last post by:
I am trying to write a report that displays a hierarchy such as: Director, Manager, Employee I want to suppress the repeating Director, Manager for the employees. This is my code: sSQL = "SELECT FY99Info.MDName, FY99Info.MgrName, FY99Info.firstname, FY99Info.lastname, FY99Info.grade FROM FY99Info WHERE left(FY99Info.orgcode,5) = '" &...
2
1412
by: keithsimpson3973 | last post by:
Hi, working in Access 2003. In my report, I have a field that contains a qty. If the qty is equal to zero , then I would like access not to display that record on the report. I thought you could do something like "On Print" or something like that? Is it possible to even do it? Thank you
12
2833
by: jenniferhelen | last post by:
I would like to suppress a row of data if each of my 5 currency columns contains a zero. An example of 1 record is below: Col 1, col 2, col 3, col 4, col 5, col 6, col7, col 8 Smith, 2008, qtr4, 0, 0, 0, 0, 0 So if columns 4 through 8 all contain zeros then I don’t want this record to appear in my report. I...
0
1691
by: dragon52 | last post by:
Hi there, I have records in the form of scorecards. Each scorecard has fields "type" and "score". eg typeA 2.3 typeB 3.3 typeA 2.0 typeC 1.9 typeB 1.1
3
2660
mseo
by: mseo | last post by:
hi, I have a report connected to a form where I can print the invoices between the two dates in the form but I have the problem: when the checkbox = true refers to Canceled invoice but the values in this record still as it is, and I want to make the values equal zero if the checkbox = true, I tried hardly to make them equal zeros but the record...
0
7626
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...
0
7549
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7830
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8061
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...
0
6180
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5454
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...
0
3599
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...
1
2044
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
1
1161
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.