To select null in a cross tab as 0

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hi,
I have a cross tab output as follows:
category Total Of CountOfdefect code Bad Debt Defective Parts Lost /Damaged
Customer Property Packaging/Design Paper Work Errors Tool Amortization
C/C 4 1 1 1 1
CAR 1 1
SCAR 2 1 1

Is there any way to do a cross tab such that the blank cells will be zero.
I appreciate any help to resolve this. Thanks.
 
Of course you can do that (you can do almost anything in Access). Look at
this
http://www.dbforums.com/microsoft-access/972784-avoid-empty-cell-crosstab-query.html

You do know a bit of SQL, right. I assume you do if you're working with
Crosstabs!

Here's another example:
http://www.access-programmers.co.uk/forums/showthread.php?t=160690

Go ahead and download 'Crosstab.zip'. I don't thin they did the query with
zeros, like you want, but that's fine, just pop in this SQL and you'll get
your desired results:
TRANSFORM Val(nz(Sum([tblSales.Sales]),0)) AS SumOfSales
SELECT tblSales.CustName
FROM tblSales
GROUP BY tblSales.CustName
PIVOT Format([tblSales]![Date1],"yyyy-mm") In
("2008-01","2008-02","2008-03","2008-04","2008-05","2008-06","2008-07","2008-08","2008-09","2008-10","2008-11","2008-12");


HTH,
Ryan---
 
Back
Top