>hi all,
>
>is there away to make this rubbish code as one select statment
>

>SELECT column5 , column6,column7,count(column5) as total_cfil from new1 WHERE  column6="A" AND column7>="2005" AND column7<="2006" ;
> AND count(column5) as total_cfil1  WHERE  column6="A " AND column7="2007";
> AND count(column5) as total_cfil2  WHERE  column6="A " AND column7="2008";
> AND count(column5) as total_cfil3  WHERE  column6="A" AND column7="2009";
> ************************
> SELECT column5 , column6,column7,count(column5) as Atotal_cfil from new1 WHERE column6="B" AND column7>="2005" AND column7<="2006" ;
> AND count(column5) as Atotal_cfil2  WHERE  column6="B " AND column7="2007";
> AND count(column5) as Atotal_cfil3  WHERE  column6="B" AND column7="2008";
> AND count(column5) as Atotal_cfil3  WHERE  column6="B" AND column7="2009";
>
>*****************************************************************
> SELECT column5 , column6,column7,count(column5) as Btotal_cfil from new1 WHERE column6="B" AND column7>="2005" AND column7<="2006" ;
> AND count(column5) as Btotal_cfil2  WHERE  column6="C " AND column7="2007";
> AND count(column5) as Btotal_cfil3  WHERE  column6="C" AND column7="2008";
> AND count(column5) as Btotal_cfil3  WHERE  column6="C" AND column7="2009";
>
> group by column6,column5,column7 INTO cursor mucurt
>brow
>
>

>thanks

Try - idea only

select column5 , column6,column7, sum(IIF(column6 = 'A',1,0)) as Total_cFil,
etc.

from New 
group by group by column6,column5,column7 INTO cursor mucurt

Use SUM(IIF for each of your counts and move where expressions into IIF expression.