Group By Query - no zero values in the output

[jpvonhemel]

I have a query that counts the number of records for each department in my table by a date range. If a department is not represented, I need to know so we can contact them.



The count seems like it ignores nil values and NZ() did not fix the problem. As always, I seem to have problems when nil values are involved and need help!

Here is the code.

SELECT tblSharepointHHSurveys.fldDept, NZ(Count(tblSharepointHHSurveys.fldDept),0) AS Denominators
FROM tblSharepointHHDepartments RIGHT JOIN tblSharepointHHSurveys ON tblSharepointHHDepartments.fldUnit = tblSharepointHHSurveys.fldDept
WHERE (((Format([fldDate],"Short Date")) Between [Forms]![frmDateRange]![fldFromDate] And [Forms]![frmDateRange]![fldThruDate]))
GROUP BY tblSharepointHHSurveys.fldDept;

Thank you very much,

Jerold

[pbaldy]

Try a LEFT join instead of RIGHT. You may also want to group on the department from the departments table.

[jpvonhemel]

pbaldy, I tried your suggestion, and the output is essentially the same. Here is the sql with your suggestions.


SELECT tblSharepointHHDepartments.fldUnit, NZ(Count(tblSharepointHHSurveys.fldDept),0) AS Denominators
FROM tblSharepointHHDepartments LEFT JOIN tblSharepointHHSurveys ON tblSharepointHHDepartments.fldUnit = tblSharepointHHSurveys.fldDept
WHERE (((Format([fldDate],"Short Date")) Between [Forms]![frmDateRange]![fldFromDate] And [Forms]![frmDateRange]![fldThruDate]))
GROUP BY tblSharepointHHDepartments.fldUnit;

[pbaldy]

Just worked in a brief test. I suspect it's because you have a field from the right side table in the WHERE clause, which in my test does screw it up. Offhand the only way I can think of to get around it would be to create a query with a criteria that got the base data from the table without any grouping, and use that on the right side instead of the actual table, and of course take the criteria out of this one.

[jpvonhemel]

So the right side is tblSharepoitnHHSurveys (user entered records) and the left side is the tblSharepointHHDepartments (static department records that match the fldDept field in the survey table)? fldDate lives in the survey table.

[jpvonhemel]

Just want to make sure I understand. Make a query that outputs each record in the survey table with an inner join to the department table. Then, create the grouping query using this query as the source?

[pbaldy]

No, make a query that just gets the survey data with the date criteria, then use that in the grouping query with the left join on the department table.

[jpvonhemel]

Hi Paul,

I created the query and then left joined on the departments. nz now works and the nil departments counted, but it is a cumulative count of the nils. I need it itemized.

Here is my sql and a sample output. I have 43 departments with > 0 observations and a total of 72 departments in the departments tables. The difference is the 29 you see in the first line of the output.

Thank you very much for taking the time to help me.

jerold

SELECT [qryHHSharepointSurvey Line List].fldDept, NZ(Count(tblSharepointHHDepartments.fldUnit),0) AS Observations
FROM tblSharepointHHDepartments LEFT JOIN [qryHHSharepointSurvey Line List] ON tblSharepointHHDepartments.fldUnit = [qryHHSharepointSurvey Line List].fldDept
GROUP BY [qryHHSharepointSurvey Line List].fldDept;


fldDept Observations
29
2 East 20
2 NA/2 NSR 15
3 North Skilled 27
3 South 23
3 Southwest 21
4 East 11
4 SW/ICU 8
4 Tower 14
5 East 11
5 SW/L&D 8
5 Tower 22
Administration 2
Admissions 4
Adol 3
Biomed 2
Business Office 4
Cath Lab 8
CCU 50
Continuing Care 21
Diabetes Care Center 15
Diagnostic Imaging 9
Dietary 23
Emergency Department 20
Endo/Special Procedures 12
Environmental Services 12
Float Pool 55
Homecare 1
Infection Prevention 4
Peds 2
Laboratory 31
Maintenance 8
Materials Management 10
Med Staff Services 1
NICU 27
Other 1
Outpatient Rehab 4
West Surgery/PACU 22
Respiratory Therapy 14
Same Day Surgery 11
Security 9
Surgery 13
Transportation 19
Wound Care Center 2

[jpvonhemel]

I was able to get all units in a list by changing the group field to a field from the department table. The problem I have now is they are all 1 and should be zero because they have no observations.

Thanks,

Jerold

[pbaldy]

You missed this:

You may also want to group on the department from the departments table.

In other words, you're selecting/grouping on the department in the transaction table, you want to use the one in the departments table.

[pbaldy]

Geez, this is what happens when you reply based on the notification email. I didn't see your update.

Can you post the db here, or a representative sample? In my test, I correctly got zeros.

[jpvonhemel]

Here is a subset that shows the 1's for departments without an associated observation record.

Thanks,

Jerold

handhygiene.accdb

[jpvonhemel]

I am using 11/01/2012 through 11/30/2012 as my date range.

Jerold

[pbaldy]

In the query in the sample you had Count(*) which was including both tables, so each had 1 record in the department table. Try this, which is returning 0 for the first 8 departments:

SELECT tblSharepointHHDepartments.fldUnit, nz(Count([qryHHSharepointSurveyLineList].[ID]),0) AS Observations
FROM tblSharepointHHDepartments LEFT JOIN qryHHSharepointSurveyLineList ON tblSharepointHHDepartments.fldUnit = qryHHSharepointSurveyLineList.fldDept
GROUP BY tblSharepointHHDepartments.fldUnit
ORDER BY nz(Count([qryHHSharepointSurveyLineList].[ID]),0);

[jpvonhemel]

Paul,

That worked. Thank you very much! It saves us a few hours a week in manual tracking.

Jerold