3rd time a charm?

[Keith]

3rd time a charm? I have a crosstab Q that we use for our
sales force that tells use the category of the sale and
who made the sale. All of the sales fall into the
following categories:

So the crosstab Q looks like this
Sales Cat header
Rep Name Last4 Total GMA GFA SMA SFA CMA CFA
Smith 1234 4 1 1 1 1
Jones 6598 1 1
White 5987 3 1 1 1

I would like the blank sales Cat GFA for Smith to have
a "0" in it. And the same for all of the other blanks.

I have had some feed back on this. Use NZ (Null-to-Zero)
but I don't know where to put the code in the crosstab Q.

Here is the crosstab in SQL.


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");

 

[Duane Hookom]

Did you check my response to your previous question?

 

[keith]

Yes. I do not know what you mean by creating the value?
the code you gave me was TheVal:Val( Nz( Sim([Qty]),0) ).

Sorry that I keep woking this.

Thanks Duane!!!!

-----Original Message-----
Did you check my response to your previous question?

--
Duane Hookom
MS Access MVP

3rd time a charm? I have a crosstab Q that we use for our
sales force that tells use the category of the sale and
who made the sale. All of the sales fall into the
following categories:

So the crosstab Q looks like this
Sales Cat header
Rep Name Last4 Total GMA GFA SMA SFA CMA CFA
Smith 1234 4 1 1 1 1
Jones 6598 1 1
White 5987 3 1 1 1

I would like the blank sales Cat GFA for Smith to have
a "0" in it. And the same for all of the other blanks.

I have had some feed back on this. Use NZ (Null-to- Zero)
but I don't know where to put the code in the crosstab Q.

Here is the crosstab in SQL.


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");

.