Crosstab Query - Populating 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");

Thanks.
 
T

Todos Menos [MSFT]

you don't need crosstab, you need analysis services

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

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
 
S

Susie Johnson

Tony;

maybe you should learn SSAS

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



Tony Toews said:
"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
 
J

James A. Fortune

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

Thanks.

Maybe:

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

James A. Fortune
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top