I did this in 4 queries though someone else may have a better plan
a) Union query to get all the months from both date fields
Code:
SELECT Year([DateAssigned]) & "-" & Month([DateAssigned]) AS YearMonth
FROM Table1
GROUP BY Year([DateAssigned]) & "-" & Month([DateAssigned]);
UNION SELECT Year([DateCompleted]) & "-" & Month([DateCompleted]) AS YearMonth
FROM Table1
GROUP BY Year([DateCompleted]) & "-" & Month([DateCompleted]);
b) Count by month for assigned
Code:
SELECT Year([DateAssigned]) & "-" & Month([DateAssigned]) AS YearMonth, Count(Table1.DateAssigned) AS CountOfDateAssigned
FROM Table1
GROUP BY Year([DateAssigned]) & "-" & Month([DateAssigned]);
c) Count by month for completed
Code:
SELECT Year([DateCompleted]) & "-" & Month([DateCompleted]) AS YearMonth, Count(Table1.DateCompleted) AS CountOfDateCompleted
FROM Table1
GROUP BY Year([DateCompleted]) & "-" & Month([DateCompleted]);
d) final query joining the union query to the other two using outer joins to get all months & Nz function to get zeroes
Code:
SELECT qryYearMonth.YearMonth, Nz([CountOfDateAssigned],0) AS CountAssigned, Nz([CountOfDateCompleted],0) AS CountCompleted
FROM qryCountAssignedMonth RIGHT JOIN (qryCountCompletedMonth RIGHT JOIN qryYearMonth ON qryCountCompletedMonth.YearMonth = qryYearMonth.YearMonth) ON qryCountAssignedMonth.YearMonth = qryYearMonth.YearMonth;
Results: NOTE that I may not have copied your data correctly
YearMonth |
CountAssigned |
CountCompleted |
2012-11 |
2 |
0 |
2012-12 |
2 |
3 |
2013-1 |
4 |
1 |
2013-2 |
0 |
3 |
2013-3 |
2 |
1 |
2013-4 |
0 |
2 |