Sub-report count issue

[fat controller]

Happy New Year to you all

I have designed a very small sub report, the intention of which is to give a total number of complaints lodged against each individual employee in a specified timeframe, however I am getting something wrong somewhere and I cannot for the life of me get my head around it.

Here is the SQL so far:

SELECT PublicComplaints.EmployeeNumber, Count(PublicComplaints.EmployeeNumber) AS “NumberOfComplaints”
FROM PublicComplaints
WHERE (((PublicComplaints.DateProcessed) Between [Forms]![DateSelect]![txtStartDate] And [Forms]![DateSelect]![txtEndDate]))
GROUP BY PublicComplaints.EmployeeNumber, PublicComplaints.ID
HAVING (((Count(PublicComplaints.EmployeeNumber))>0))
ORDER BY Count(PublicComplaints.EmployeeNumber) DESC;

When I run the sub-report, it does return a number of records, but lists them all individually - for example, it will show:

5501112 - 1
5509448 - 1
5509448 - 1
5509448 - 1
5509448 - 1
5078898 - 1

Whereas I would like it to return:

5501112 - 1
5509448 - 4
5078898 - 1

Any idea where I am going wrong?

 

[arnelgp]

why do you have double quotes on the calculated field NumberOfComplaints?

 

[plog]

GROUP BY PublicComplaints.EmployeeNumber, PublicComplaints.ID

Why are you grouping by a field that isn't in the SELECT? That's the issue.

Be environmentally conscious with your SQL--only use the parts of the table you need. With that said, you can also ditch the HAVING clause.

 

[fat controller]

GROUP BY PublicComplaints.EmployeeNumber, PublicComplaints.ID

Why are you grouping by a field that isn't in the SELECT? That's the issue.

Be environmentally conscious with your SQL--only use the parts of the table you need. With that said, you can also ditch the HAVING clause.

Thank you Works perfectly now. That's what I get for copying a sub-report, bending it a little and expecting it to just work. The ID was left over from the other sub-report.

why do you have double quotes on the calculated field NumberOfComplaints?

Simply because I didn't know any better at the time the original sub-report was created, and I copied that sub-report and bent it to fit this purpose.

Thanks guys