problem with sum(case 1

[edvil]

Hello,

I am trying to do a sum(case statement but not sure if I am going the right way about it

This is what my sql statement looks like I ge a syntax error
in the first sum(case

select [Sales Jan 2007 Through Oct 2009].FEKNNB, [Sales Jan 2007 Through Oct 2009].FEAAC3, [Sales Jan 2007 Through Oct 2009].DDAITX,
sum(case [Sales Jan 2007 Through Oct 2009].year when 07 then [Sales Jan 2007 Through Oct 2009].DDDPVA, else 0 end)GRS07,
sum(case [Sales Jan 2007 Through Oct 2009].year when 07 then [Sales Jan 2007 Through Oct 2009].TOTALCOST, else 0 end)COST07,
sum(case [Sales Jan 2007 Through Oct 2009].year when 08 then [Sales Jan 2007 Through Oct 2009].DDDPVA, else 0 end)GRS08
sum(case [Sales Jan 2007 Through Oct 2009].year when 08 then [Sales Jan 2007 Through Oct 2009].TOTALCOST, else 0 end)COST08
sum(case [Sales Jan 2007 Through Oct 2009].year when 09 then [Sales Jan 2007 Through Oct 2009].DDDPVA, else 0 end)GRS09
sum(case [Sales Jan 2007 Through Oct 2009].year when 09 then [Sales Jan 2007 Through Oct 2009].TOTALCOST else 0 end)COST09
from [Sales Jan 2007 Through Oct 2009].year group by [Sales Jan 2007 Through Oct 2009].year.feknnb, [Sales Jan 2007 Through Oct 2009].yearfeaac3

 

[sxschech]

If this is an MS-Access query and not a pass-thru odbc query, you should use IIf instead.

sum(IIf([Sales Jan 2007 Through Oct 2009].year = 07,[Sales Jan 2007 Through Oct 2009].DDDPVA, 0)) AS GRS07

 

[edvil]

what I was trying to accomplis is to do a crosstab query with two values.

 

[SkipVought]

If this is an MS-Access query and not a pass-thru odbc query, you should use IIf instead.

Your reply was not relevant to sxschech's reply to you.

The issue has nothing at all to do with a crosstab.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

There were a lot of issues, if this is truly an Access query. Try something like the following that assumes the Year is a string data type.

select FEKNNB, FEAAC3, DDAITX,
sum(Abs([year] = "07") * DDDPVA) as GRS07,
sum(Abs([year] = "07") * TOTALCOST) as COST07,
sum(Abs([year] = "08") * DDDPVA) as GRS08,
sum(Abs([year] = "08") * TOTALCOST) as COST08,
sum(Abs([year] = "09") * DDDPVA) as GRS09,
sum(Abs([year] = "09") * TOTALCOST) as COST09,
from [Sales Jan 2007 Through Oct 2009] 
group by feknnb, yearfeaac3,DDAITX;

Duane
Hook'D on Access
MS Access MVP

 

[edvil]

SkipVought, you are correct. I apologize for not posting things in the correct way.
dhookom, I tried what you suggested and... Thank you!!! that is what I intented to acomplish. I really appreciate all the help.