hiding unnecessary fields in Access Report

Question

At my workplace there is a "Daily Feedback" database where details are entered of any errors made by Customer Service Officers (CSOs), such as who made the mistake, when, and what service it was for. this is so we can gather data which would show areas where CSO's are repeatedly making mistakes so we can feed this back to them and train them in those areas if need be.
i currently have a report where an CSOs name is entered along with a date range and it produces the report showing the number of errors made for each service for that date range.
the code used is -
=Sum(IIf([Service]="Housing",1,0))
=Sum(IIf([Service]="Environmental Health",1,0))
etc etc for each Service.
The problem i have is that not every CSO does EVERY service and so there are usually a few results showing as "0". and i cannot be sure if thats because they dont do the service or if they are just very good at that service.
Being absolutely useless at SQL (or any other possible way of fixing this) i cannot figure out how to HIDE the entries that produce the zero value.
any help here would be greatly appreciated!

The best way to do this is with aggregate queries. "Being absolutely useless at SQL" ... are you open to changing the form record source? Another technique might be conditional formatting (Access 2007 and later). — nicholas

nicholas nicholas · Accepted Answer · 2012-07-05T16:03:36

Assuming you have a table with the fields CSO, Service, FeedbackComments you could modify the report record source to

SELECT [CSO], [Service], Count([FeedbackComments]) 
FROM [FeedbackTable] 
GROUP BY [CSO], [Service];

Then services which have no records will not appear on the report.

hiding unnecessary fields in Access Report

2 Answers