I'm having some trouble getting a query to correctly count values.
I have 3 tables: Projects, Departments, and ProjectContributions. Projects.Department stores an ID which is supposed to match a record in the Departments table. ProjectContributions has records with a ProjectID and a monetary value.
I am trying to sum those monetary values based on the project's department and count the number of projects in each department. I had used 'DCount("ProjectID","Projects","Department = '" & [Departments_All]![OrgTextID] & "'") AS ProjectCount', but i couldn't get it to count the projects where Projects.Department didn't match a record in the Departments table.
Here is the query i have so far which appears to be counting the number of related records in the ProjectContribution table rather than the number of projects with each department.
SELECT Departments_All.OrgTextID, Count(Projects.Department) AS CountOfDepartment, Sum(IIf(ProjectContributions!ContributionType=2,Pr ojectContributions!USDAmount)) AS Loan, Sum(ProjectContributions!USDAmount) AS Total FROM (Projects LEFT JOIN ProjectContributions ON Projects.ProjectID = ProjectContributions.ProjectID) LEFT JOIN Departments_All ON Projects.Department = Departments_All.OrgTextID GROUP BY Departments_All.OrgTextID;