>>>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,
>
>How about:
>
>nYear = YEAR(DATE())
>dToday = DATE()
>
>SELECT cCommodity, SUM(nAmount) AS YTD, 10000-10000 AS today FROM tTable WHERE YEAR(dDate)=nYear ;
> UNION ;
> (SELECT cCommodity, 0, SUM(nAmount) FROM tTable WHERE dDate=dToday) ;
> GROUP BY 1;
> INTO ...
>
>John.

And you will have 2 records for each Commodity, exactly what Ian tries to avoid! :)

Vlad