Excellent. Thanks again.
Allen

>IN VFP9 you can use select in a column but it may be less efficient.

>SELECT DISTINCT stationno, ;
>	(SELECT sum(mt1.colamt1) FROM mytable mt1 JOIN mt ON mt.stationno = mt1.stationno WHERE tranno=1) AS colamt1, ;
>...;
>from mytable mt ;
>INTO CURSOR tmp NOFILTER
>

>
>>Thank you Sergey.
>>Just curious, is there a way to do it without the iif statements?
>>Allen
>>
>>>You don't need DISTINCT with GROUP BY

SELECT stationno, ;
>>>	sum(IIF(tranno=1,colamt1, 0000000.00)) AS colamt1, ;
>>>	sum(IIF(tranno=2,colamt2, 0000000.00)) AS colamt2 ;
>>>from mytable ;
>>>GROUP BY stationno ;
>>>INTO CURSOR tmp NOFILTER
>>>