Sum query based off two columns

[papadega3000]

I have a problem trying to sum a numeric column based off another column in the same table.
What I am trying to accomplish is to sum a numeric column if a corresponding column is Not Null.

I have uploaded my sample DB that to illustrate my problem. The query I am testing with is called Query1 and I am looking at summing point values from the task table grouped by the test case those tasks are in. I can easily sum the total number of points for each test case. But when I add another field to the query to sum the point values of the task in the test case that have a date entered into its corresponding date field it will not sum them correctly.
The result I get is the same sum total is shown for all three fields in the query.

I have tried several attempts at making criteria for this to work but nothing seems to work the way I want it too.

Here are my attempts:
IIf(Not (Sum([Task].[Points]))=IsNull("Attempted_Actual"),Sum([Task].[Points]),0)

IIf(Not (Sum([Task].[Points]))=IsNull("Completed_Actual"),Sum([Task].[Points]),0)

Not IsNull("Attempted_Actual")

Not IsNull("Completed_Actual")

My question is:

Is it possible to do want I want all in one select query or would this be something to code in a VBA module and then just call that module in a report?

My plan was to do this all in a query then build a report to display this query.

I have attached my dummyDB file and you can look at the Test_Case Table and expand each test case to see the data I have populated.

Any guidance would be appreciated.

Thanks,

 

[papadega3000]

I realized the DB never got attached.

Sorry about that.

 

[Brianwarnock]

Doing what you trying to do even iof we correct the syntax will give the the same Total points in each heading as the criteria applies to each record not individual fields, ie a record is selected or not. You need to put your condition in the fields the SQL would be

SELECT Run.Test_Case, Sum(Task.Points) AS Total_points, Sum(IIf(Not IsNull([Attempted_Actual]),[Points],0)) AS Attempted, Sum(IIf(Not IsNull([Completed_Actual]),[Points],0)) AS Completed
FROM Run INNER JOIN Task ON Run.Run = Task.Group
GROUP BY Run.Test_Case;

At least I think that's what you are attempting.

Brian

 

[papadega3000]

Hello,

I tried that same setup before using the Query Builder or what I thought was the same thing and it was erroring out. However, I copied and pasted the SQL code into the query and now I see where I was going wrong. The error was in reference to the epression being wrong and I apparently was trying to sum a sum which wasn't working.
All in all your suggestion works perfectly. I can now hopefully manipulate this on some other expressions I want to add to this query.


Thanks for the tip. Hopefully I can use it as a guide.

 

[Brianwarnock]

Glad I could help.

Brian