Crosstab Query - Populating with Zeros

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

Thanks.

 

[Todos Menos [MSFT]]

you don't need crosstab, you need analysis services

SSAS is 100,000 times more powerful than a relational crosstab

 

[Tony Toews [MVP]]

you don't need crosstab, you need analysis services

SSAS is 100,000 times more powerful than a relational crosstab

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Susie Johnson]

Tony;

maybe you should learn SSAS

Displaying empty rows as a 0 is a simple setting in SSAS

"T o d o s M e n o s [ M S F T ]" <t o d o s _ m e n o s _ m (e-mail address removed)>

wrote:

you don't need crosstab, you need analysis services

SSAS is 100,000 times more powerful than a relational crosstab

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[James A. Fortune]

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

Thanks.

Maybe:

Sum(Val(Nz([Qry_AIT_FSE _COGS_Summary].[SumOfMonthly Sales],0)))

James A. Fortune
(e-mail address removed)