>>I am trying to build a view that totals an amount filed, I wish to have one column total be a YTD total and another column for just a particular day. I have tried using a join but that gives two rows for the result, I need one row with both totals.
>>
>>TIA
>>
>>Ian
>
>How about something like:
>
>SELECT cCommodity,sum(nAmount) as YTD, sum(iif(dDate=date(),nAmount,0)) as Today FROM YourTable GROUP BY 1
>
>HTH
>Elyse
Elyse

Thanks for the help. the following did the trick:

SELECT Daily.group, Daily.code, ;
SUM(iif(daily.date < ?which_date AND daily.sh_re = "R",Daily.net,0)) as YTD_WET, ;
SUM(iif(daily.date < ?which_date AND sh_re = "R",Daily.dry,0)) as YTD_DRY, ;
SUM(iif(daily.date < ?which_date AND sh_re = "S",Daily.net,0)) as YTD_S, ;
SUM(iif(daily.date = ?which_date AND sh_re = "R",Daily.net,0)) as DAY_WET, ;
SUM(iif(daily.date = ?which_date AND sh_re = "R",Daily.dry,0)) as DAY_DRY, ;
SUM(iif(daily.date = ?which_date AND sh_re = "S",Daily.net,0)) as DAY_S ;
FROM wits!daily ;
GROUP BY Daily.group, Daily.code ;

Thanks also to all the others who offered solutions.