I know I'm missing something simple, but am not seeing it. I'm starting with a table (called view_project_with_attributes) and it has one record per fund - a total of about 4100 funds. I'm trying to bring in the balances for a couple of account codes so have done a sub query where I am summing the dollars and grouping by the projectdimid (which is the fund). Instead of getting 4100 records, I'm getting over a million. I've tried doing this code multiple ways and it just isn't working. What am I missing?
SELECT View_Project_with_Attributes.ProjectDimID, View_Project_with_Attributes.ProjectID, View_Project_with_Attributes.ProjectDescription,
View_Project_with_Attributes.ProjClass, View_Project_with_Attributes.ProjCollege, View_Project_with_Attributes.ProjDept, Q1400.Balance1400,
Q1405.Balance1405
FROM View_Project_with_Attributes
LEFT OUTER JOIN
(SELECT ProjectDimID, SUM(NaturalAmount) AS Balance1400
FROM FACT_GLTransactionDistribution
WHERE (AccountCode = '1400') AND (PostDate <= '3/31/14') AND (AccountCategorySystemID IN ('1'))
GROUP BY ProjectDimID) AS Q1400 ON View_Project_with_Attributes.ProjectDimID = Q1400.ProjectDimID
LEFT OUTER JOIN
(SELECT ProjectDimID, SUM(NaturalAmount) AS Balance1405
FROM FACT_GLTransactionDistribution AS FACT_GLTransactionDistribution_1
WHERE (AccountCode = '1405') AND (PostDate >= '7/1/01') AND (PostDate <= '3/31/14') AND (AccountCategorySystemID IN ('1'))
GROUP BY ProjectDimID) AS Q1405 ON View_Project_with_Attributes.ProjectDimID = Q1400.ProjectDimID
HAVING (View_Project_with_Attributes.ProjClass IN ('Endowed', 'Quasi Endowed', 'Endowed - Non Invested'))