Crosstab default "0"

[Keith]

I have been working on this and I don't think that I'm
getting the Code. I have benn told that I should post it.
I have a crosstab Q that I need the field to have a "0" if
null.

TRANSFORM Count([DTPInGrossNumbersFYLookUp Q].DEPInDate)
AS [The Value]
SELECT [DTPInGrossNumbersFYLookUp Q].RC,
[DTPInGrossNumbersFYLookUp Q].[RCTR Last 4], Count
([DTPInGrossNumbersFYLookUp Q].DEPInDate) AS [Total Of
DEPInDate]
FROM [DTPInGrossNumbersFYLookUp Q]
WHERE ((([DTPInGrossNumbersFYLookUp Q].MissionCAT)="GMA"
Or ([DTPInGrossNumbersFYLookUp Q].MissionCAT)="GFA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="SMA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="SFA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="CMA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="CFA"))
GROUP BY [DTPInGrossNumbersFYLookUp Q].RC,
[DTPInGrossNumbersFYLookUp Q].[RCTR Last 4]
PIVOT [DTPInGrossNumbersFYLookUp Q].MissionCAT In
("GMA","GFA","SMA","SFA","CMA","CFA");

I hope this helps.
And thanks to all for all your help.
Keith

 

[[MVP] S. Clark]

The problem is probably the fact that one of your MissionCAT codes does not
have any DEPInDate records, thus the desired MissionCAT code does not get
returned in the recordset.

If you need ALL MissionCAT codes to be returned, then you will need to
create an Outer Join between the MissionCat table, that holds all of the
codes, and count query. The following example shows a count of orders per
customer. Because of the outer join, it will show ALL customers, (event
those that do not have any orders), and their counts.

SELECT Customers.CustomerID, Customers.CompanyName, Count(Orders.OrderID) AS
CountOfOrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName;

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Keith]

The MissionCAT is a feild in the DEPInDate record table.
and I have it so the Crosstab Q will only pull MissionCAT
with the criteria of
("GMA","GFA","SMA","SFA","CMA","CFA"), so when I run the
drosstab some come back null. I would like that null to
show a 0.

-----Original Message-----
The problem is probably the fact that one of your MissionCAT codes does not
have any DEPInDate records, thus the desired MissionCAT code does not get
returned in the recordset.

If you need ALL MissionCAT codes to be returned, then you will need to
create an Outer Join between the MissionCat table, that holds all of the
codes, and count query. The following example shows a count of orders per
customer. Because of the outer join, it will show ALL customers, (event
those that do not have any orders), and their counts.

SELECT Customers.CustomerID, Customers.CompanyName, Count (Orders.OrderID) AS
CountOfOrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Customers.CompanyName;

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I have been working on this and I don't think that I'm
getting the Code. I have benn told that I should post it.
I have a crosstab Q that I need the field to have a "0" if
null.

TRANSFORM Count([DTPInGrossNumbersFYLookUp Q].DEPInDate)
AS [The Value]
SELECT [DTPInGrossNumbersFYLookUp Q].RC,
[DTPInGrossNumbersFYLookUp Q].[RCTR Last 4], Count
([DTPInGrossNumbersFYLookUp Q].DEPInDate) AS [Total Of
DEPInDate]
FROM [DTPInGrossNumbersFYLookUp Q]
WHERE ((([DTPInGrossNumbersFYLookUp Q].MissionCAT)="GMA"
Or ([DTPInGrossNumbersFYLookUp Q].MissionCAT)="GFA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="SMA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="SFA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="CMA" Or
([DTPInGrossNumbersFYLookUp Q].MissionCAT)="CFA"))
GROUP BY [DTPInGrossNumbersFYLookUp Q].RC,
[DTPInGrossNumbersFYLookUp Q].[RCTR Last 4]
PIVOT [DTPInGrossNumbersFYLookUp Q].MissionCAT In
("GMA","GFA","SMA","SFA","CMA","CFA");

I hope this helps.
And thanks to all for all your help.
Keith

.

 

[Duane Hookom]

Try substitute this into your SQL:
TRANSFORM Val( Nz(Count(DEPInDate),0) ) AS [The Value]

You should also be able to simplify your WHERE by removing it

TRANSFORM Val(Nz(Count(DEPInDate) ,0)) AS [The Value]
SELECT RC, [RCTR Last 4],
Count(DEPInDate) AS [Total Of DEPInDate]
FROM [DTPInGrossNumbersFYLookUp Q]
GROUP BY RC, [RCTR Last 4]
PIVOT MissionCAT In ("GMA","GFA","SMA","SFA","CMA","CFA");

Your In clause will limit the returned records.