Hank.School
Registered User.
- Local time
- Yesterday, 22:09
- Joined
- Oct 14, 2016
- Messages
- 39
Hi guys,
Trying to develop what should be a straight forward report but having problems with the queries. Is there a way to return multiple columns in a query with sums of a particular field under different criteria?
To be more specific, I am trying to write a query which returns the sum of the number of hours for particular jobs/subjobs. There is a field in the table which indicates if a particular task is complete and I would like a separate column which sums the same number of hours field with a criteria of 'StatusCode = 5' (task is complete).
So I would want my query to return (for a particular job #) a listing, Grouped By each subpart, three columns... the subpart #, the Sum() of all task hours under the subpart, and the Sum() of all task hours under the subpart with a status code = 5.
I haven't been able to write this into a single query so I have been using subreports, each with a piece of the puzzle. That is causing its own problems which I have found SQL explanations for but don't understand and can't make work yet. I will tackle that later, I am just trying to find out if there is a way to add another Sum column to this query which only adds NumHours under tasks which are complete:
Is this possible?
Thanks
Hank
Trying to develop what should be a straight forward report but having problems with the queries. Is there a way to return multiple columns in a query with sums of a particular field under different criteria?
To be more specific, I am trying to write a query which returns the sum of the number of hours for particular jobs/subjobs. There is a field in the table which indicates if a particular task is complete and I would like a separate column which sums the same number of hours field with a criteria of 'StatusCode = 5' (task is complete).
So I would want my query to return (for a particular job #) a listing, Grouped By each subpart, three columns... the subpart #, the Sum() of all task hours under the subpart, and the Sum() of all task hours under the subpart with a status code = 5.
I haven't been able to write this into a single query so I have been using subreports, each with a piece of the puzzle. That is causing its own problems which I have found SQL explanations for but don't understand and can't make work yet. I will tackle that later, I am just trying to find out if there is a way to add another Sum column to this query which only adds NumHours under tasks which are complete:
Code:
SELECT Sum(TBL_TaskList.NumHours) AS SumOfNumHours
FROM TBL_JobSiteEntries INNER JOIN TBL_TaskList ON TBL_JobSiteEntries.SiteEntry = TBL_TaskList.SiteID
GROUP BY TBL_JobSiteEntries.SubPart;
Thanks
Hank