>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?
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