Returning Sum() from same field with different criteria (1 Viewer)

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

Hi Hank. Have you tried using a subquery?

I'm trying to make sense of the question. The only way I can read it is to say, I doubt you can do what you want in ONE query though there might be a way to divide and conquer what you seek.

IF I have read this correctly, you want a sum for records with StatusCode 5 but you ALSO want a sum for all records regardless of the group. You can't do that in a single WHERE clause because you are asking for something that IS showing a particular status code and at the same time seeking something that is NOT showing that same status code. You can't write a WHERE clause like that because the whole query has to use the same WHERE clause and you have contradictory requirements for the two fields.

You might look into the idea of making TWO queries to give you the two different sums, then joining these two queries in an overarching query. You might also look into the use of sub-queries, which might also do the trick. You CAN have two different WHERE clauses in a query with sub-queries because the sub-queries each have a WHERE clause of their own.

EDIT: I see our friend theDBguy also suggests sub-queries.

Do you mean a separate query (either on a subreport or stand alone) which provides the extra data? Yes... that is how I am currently trying to do it but that is causing other problems (with job subparts that have no completed tasks). I thought if I could work it into a single query (Maybe with an IIF on the new column??) that would fix the problem I am having using subqueries.

Thanks
Hank

Hi Hank. What is this problem you're having?

The_Doc_Man said:

IF I have read this correctly, you want a sum for records with StatusCode 5 but you ALSO want a sum for all records regardless of the group.

Click to expand...

Well, ALSO a sum of all records under a subpart regardless of the StatusCode... yes.

From what I know of Access (and the little SQL I know) I figured it was not possible to include multiple WHERE criteria. Thank you both for confirming that... it would have been a possible way out of the problem I am having using a subquery.

The problem with a subquery is that when I have a subpart with NO completed tasks, the subquery does not output a line for that subpart at all... I would like it to show a line with a '0' quantity.

I have been searching the problem for two days now and have found references to the situation and a fairly detailed article called 'Reporting what's not there'. Unfortunately, the proposed solution (to UNION the query with a forced 0 line query) doesn't seem to be working for me... The 'DISTINCT' in the proposed solution is apparently eliminating lines which have the same number of hours :banghead:

At this point, I am ready to give up on queries for this and write functions to supply the data instead... I simply don't know enough SQL to understand how to achieve this.


Thanks
Hank

Attached is a copy of the relevant section... The RPT_Totals uses the other two reports as sub-reports. The RPT_CompTaskHours only shows 2 lines because there are no completed tasks under subpart 3. I have seen various solutions proposed to fix this (and show a line 3 with '0') including the NZ function and the UNION query I mentioned in the last post but none seem to work properly.


I think it is a fairly common problem people encounter in reports but I can't seem to get it right. Any help would be appreciated.


Thanks
Hank

I would like a separate column which sums the same number of hours field with a criteria of 'StatusCode = 5' (task is complete).

Click to expand...

If you simply want a new column with criteria in your existing query you can do it like so:


You move the criteria to an IIf within the field and then Sum the field you want to sum when it evaluates true and sum 0 if false.

Bingo! That code worked flawlessly. I had seen other queries with the criteria moved to IIF statements but I couldn't figure out how to do it here. Thank you!! I will try to dissect it to figure out using IIF in SQL but I am still pretty far from understanding; I know how the conditional works but using it in queries is over my head.

Greatly appreciate the help