Month Year Count Where No Records Exist for a Department

[jpvonhemel]

I went to write a report that is grouped by MonthYear and shows the number of observations entered into a sharepoint survey. MonthYear is derived from the survey date so everything works when a record exists. My problem is trying to figure out how to group by Month/Year for those departments that have zero observations and thus zero records in my table. My desired output would be something like this.

Department a

Jan 2011 0
Feb 2011 0


Mar 2011 0

and so on.

Thank you,

Jerold

SELECT qryHHSharepointSurveyLineListALL.MonthYear, tblSharepointHHDepartments.fldUnit, tblSharepointHHDepartments.fldDirector, Nz(Count([qryHHSharepointSurveyLineListALL].[ID]),0) AS Observations
FROM tblSharepointHHDepartments LEFT JOIN qryHHSharepointSurveyLineListALL ON tblSharepointHHDepartments.fldUnit = qryHHSharepointSurveyLineListALL.fldDept
GROUP BY qryHHSharepointSurveyLineListALL.MonthYear, tblSharepointHHDepartments.fldUnit, tblSharepointHHDepartments.fldDirector
HAVING (((Nz(Count([qryHHSharepointSurveyLineListALL].[ID]),0))=0))
ORDER BY Nz(Count([qryHHSharepointSurveyLineListALL].[ID]),0);

[June7]

One method would require a table of departments and a dataset of all the possible month/year pairs. Join the two to have a dataset of all possible department/month/year combinations. Join that to the aggregate query with a join type of "Include all records from department/month/year ..."

Another method uses DLookup in textboxes on report, one for each department and for each month/year.

[jpvonhemel]

Thank you June 7. I created a new query with depts/month/year (qryHHSharepointSurveyDeptMonthYearJoin) and then joined it to include all records from the new query. I am getting counts for those months that are > 0, but not the ones that equal 0.

SELECT qryHHSharepointSurveyDeptMonthYearJoin.MoYr, qryHHSharepointSurveyDeptMonthYearJoin.fldUnit, qryHHSharepointSurveyDeptMonthYearJoin.fldDirector, Nz(Count([qryHHSharepointSurveyLineListALL].[ID]),0) AS Observations, Month([MoYr]) AS MoNum, Year([MoYr]) AS YrNum
FROM qryHHSharepointSurveyDeptMonthYearJoin LEFT JOIN qryHHSharepointSurveyLineListALL ON qryHHSharepointSurveyDeptMonthYearJoin.fldUnit = qryHHSharepointSurveyLineListALL.fldDept
WHERE (((qryHHSharepointSurveyDeptMonthYearJoin.MoYr)=[MonthYear]))
GROUP BY qryHHSharepointSurveyDeptMonthYearJoin.MoYr, qryHHSharepointSurveyDeptMonthYearJoin.fldUnit, qryHHSharepointSurveyDeptMonthYearJoin.fldDirector
ORDER BY Nz(Count([qryHHSharepointSurveyLineListALL].[ID]),0);

[June7]

How did you produce the dataset of all month/year pairs?

Want to provide db for analysis? Follow instructions at bottom of my post.

[jpvonhemel]

I created a new table with a date type field. I added 1/1/13, 2/1/13, etc records. I then created a query with an expression to format 1/1/13 to Jan 2013. I created my dept/month/year dataset from both my original department table and the single field from month year query without using any relationships. That dataset had a unique month/year for each department. I will try to make a copy and anonymize data in the tables and provide a sample.

How did you produce the dataset of all month/year pairs?

Want to provide db for analysis? Follow instructions at bottom of my post.

[jpvonhemel]

Hi June7,

Here is my db with a very modest record count. Chaplain has no observations, so I would like this listed as zero. My query is qryHHSharepointSurveyDeptNoResponseByMonth. I only see depts with records, as you should see.

Thank you very much for helping me.

Jerold

Attachment 13715

[June7]

Your attachment link doesn't work, says 'Invalid Attachment'.

[jpvonhemel]

Your attachment link doesn't work, says 'Invalid Attachment'.

That's odd. It worked last night. Let me try again. Hand Hygiene Anon.zip

[June7]

Building a table of all month/year pairs is one way to get that dataset. Also, if you are certain there will be at least one record for each month/year, that dataset can be generated by a query.

For starters, remove all filter criteria from the queries.

In qryHHSharepointSurveyDeptNoResponsesByMonth join MoYr to MonthYear as well as fldUnit to fldDept - this will be a compound join. The Observations count expression can be simpler, Nz not needed and just select Count on the Total Row: Count(qryHHSharepointSurveyLineListALL.ID) AS Observations

Now view the results. This shows a record for each Unit for each month/year. Is the Observation data correct? For instance, Oct 2012 shows 2 South as the only Unit with an observation. The query is joining fldDept with fldUnit but the data is not consistent - fldDept has '1South' and fldUnit has '1 South'. How did this happen?

You have ID field in tblSharepointHHDepartments defined as primary key but not using as foreign key. Make fldUnit the PK since this is the value saved in Surveys.