>Hi All -
>I have a select statement that seems to work OK except for one problem.
>

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

>My problem is that if there are records in ProposedFunding that are not matched in RTA-PmtForecast, I lose them. Can someone help me figure out how to provide the empty row (all zeros except for ProgramTotal) when there are no matching records in RTA-PmtForecast?
>Thanks


Maybe something like this?

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 ([TipID] = 92.04) 
ORDER BY [P.Year]

(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