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

>I have 1 table that I need to sum a certain column depending on the transno .
>such as: transno=1 then sum(colamt1) (for each stationno)
>so:
>

SELECT DISTINCT stationno, sum(colamt1) AS colamt1, 0 AS colamt2 ;
>from mytable ;
>WHERE tranno=1 ;
>INTO CURSOR tmp NOFILTER;
>GROUP BY stationno

>works fine.
>
>And if tranno=2 then I want to sum(colamt2) (for each station), setting sum of colamt1 to zero
>like:
>

SELECT DISTINCT stationno, 0 AS colamt1, sum(colamt2) AS colamt2 ;
>from mytable ;
>WHERE tranno=2 ;
>INTO CURSOR tmp NOFILTER;
>GROUP BY stationno

>
>How do I UNION these two selects? Or JOIN them? to get one table that sums colamt1 where transno=1 (while leaving colamt2 zero) AND sums colamt2 where transno=2 (while leaving colamt1 zero)
>Allen