Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Lao PDR
    Posts
    15

    Question Unanswered: Queryu counting problem

    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;

    Any assistance will be greatly appreciated.

    Thanks
    Justin

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    So you want a count of records where there are no project contributions?

    Try Adding this:

    WHERE ProjectContributions.ProjectID IS NULL
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •