Calculated query using multiple record lines to find sum of records

[access_sql_guy]

This one has been getting to me lately, I've tried running a macro in excel with the output, but decided to write a query in Access using the following summary data. Factor is what I'm trying to sum, now for each group of Factors (say the are grouped by City), I need to first determine if they are complete, and the date suggests it is completed or not, a blank means it's not completed, then sum the number of factors in the completed row. If not complete, it should populate 0, and then move onto the next record for analysis.



Sample data:

Group Factor Completiondate Num

LA	Factor1	1/1/2016	1
LA	Factor2	1/2/2016	1
LA	Factor3	1/5/2016	1
LA	Complete	1/10/2016	3

I have basically 3-4k of these rows in my query.

Then after looking at this sample I would move on to the next group, and it may have fewer or more factors.

Group Factor Completiondate Num

Chicago	Factor1	2/1/2016	1
Chicago	Factor2	2/5/2016	1
Chicago	Complete	2/8/2016	2

What do you think, I'd like to sum all the number of factor(s), then within that grouping calculate the total factors within that group to come up with the number of completed factor(s).

I assume using IIF statement within each would work, but I'm currently undecided on how to do that.

Thank you for reviewing.

[access_sql_guy]

Also, I want to keep it in a query, instead of using a report, because I'm going to export the data with the numbers for each record and run a pivot table. Each completion date will be a column and then I wanted to see how many factors and completions there were each day.

Thank you, hope that explains further what I was looking for.