Hi all,
Well it took me a few days but I managed to develop a solution to my problem.
I created two separate queries - one that counted completed competencis and one that counted planned competencies. I then built a union query and added a null field so that I could return either completed or planned competencies and sorted by month. Works a treat and boss is very happy with results. For those interested the SQL for all queries is listed below.
Code:
SELECT Count(tblTraineeCompetencyLog.DatePlanned) AS Total, Format([DatePlanned],"mmm") AS [Month], Year([DatePlanned]) AS [Year]
FROM tblTraineeCompetencyLog
GROUP BY Format([DatePlanned],"mmm"), Year([DatePlanned])
HAVING (((Count(tblTraineeCompetencyLog.DatePlanned))>0));
Code:
SELECT Count(tblTraineeCompetencyLog.DateCompleted) AS Total, Format([DateCompleted],"mmm") AS [Month], Year([DateCompleted]) AS [Year]
FROM tblTraineeCompetencyLog
GROUP BY Format([DateCompleted],"mmm"), Year([DateCompleted])
HAVING (((Count(tblTraineeCompetencyLog.DateCompleted))>0));
and finally the union query
Code:
Select Null As Planned, [Total] As Completed,[Month],[Year] FROM qryCompetenciesA
UNION ALL Select [Total],Null, [Month],[Year] As [Type] FROM qryCompetenciesB;
Cheers
Rob