Crosstab Query - populating blanks with zeros

B

Bob

I would like to populate with zeros where there are currrently blanks in
cells. I am using the folllowing SQL code and I am getting a Syntax error
missing operator message. Any sugguestions?

TRANSFORM Val(NZ(Sum([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales],0)) AS
[SumOfSumOfMonthly Sales]
SELECT [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
FROM [Qry_AIT_FSE _COGS_Summary]
GROUP BY [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
PIVOT Format([Month],"yyyy-mm");
 
K

KARL DEWEY

Try this --
TRANSFORM NZ(Sum([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales]),0) AS
[SumOfSumOfMonthly Sales]
SELECT [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
FROM [Qry_AIT_FSE _COGS_Summary]
GROUP BY [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
PIVOT Format([Month],"yyyy-mm");
 
B

Bob

Thanks, that works.
--
Bob


KARL DEWEY said:
Try this --
TRANSFORM NZ(Sum([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales]),0) AS
[SumOfSumOfMonthly Sales]
SELECT [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
FROM [Qry_AIT_FSE _COGS_Summary]
GROUP BY [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
PIVOT Format([Month],"yyyy-mm");
--
KARL DEWEY
Build a little - Test a little


Bob said:
I would like to populate with zeros where there are currrently blanks in
cells. I am using the folllowing SQL code and I am getting a Syntax error
missing operator message. Any sugguestions?

TRANSFORM Val(NZ(Sum([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales],0)) AS
[SumOfSumOfMonthly Sales]
SELECT [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
FROM [Qry_AIT_FSE _COGS_Summary]
GROUP BY [Qry_AIT_FSE _COGS_Summary].Region, [Qry_AIT_FSE _COGS_Summary].FSE
PIVOT Format([Month],"yyyy-mm");
Click to expand...
Click to expand...
 
Top