>>(I assumed FundType exists only in ProposedFunding and not in RTA-PmtForecast, if it does then change the FundType = 'RTA' to be a proper join) also it returns nulls instead of zeroes, and notice I am ordering by ProposedFunding Year instead of RTA-PmtForecast _Year as it can be null
>
>Thanks Hugo. With a little tweaking I ended up with this which seems to work.
>

>SELECT [RTA-PmtForecast].*, (jan + feb + mar + apr + may + jun+ jul + aug + sep + oct + nov + dec) as total, P.ProgramTotal, P.[Year]
>FROM [RTA-PmtForecast]
>RIGHT JOIN 
>(SELECT sum(amount) as ProgramTotal, [FundType], [TipID], [Year] FROM ProposedFunding GROUP BY FundType, TipID, Year) P 
>on P.[Fundtype]='RTA' and P.[tipID]=[RTA-PmtForecast].[TipID] and P.[YEAR]=[RTA-PmtForecast]._year
>WHERE (p.[TipID] = 92.04 and p.FundType='RTA') ORDER BY P.Year

>
>Naomi - I couldn't quite follow the cte and the semicolon. Also, the select is in a SQLdatasource so I needed it all in one line. Thanks

It can be in multiple lines, it's not a problem for SQLDataSource. You may want to remove ; in front of CTE if this is in SQLDataSource. Alternatively, you can use CTE as derived table, e.g.

select ...
from (select sum(amount), ...) cte LEFT JOIN ...

---------------
In other words, my select was very similar to Hugo's but I prefer to use LEFT JOIN as it's easier to understand (for european mind) than RIGHT JOIN.