Hiding Records in Report, Displaying Totals

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Hello! I am building a report based off of a query that only uses three
fields, a create time a user id and an incident number. I created a
report that will display these based on grouping by month on the create
time field. Using the =[RecordCount] through the control source field,
the report will display the total number of incidents for that month.

However, the report also listed each and every incident for the month
and then the total. Since I'm working with many, many records, I'd like
the report to just display the month and the total number of incidents
for the month (rather than listing each and every one). Is there any
way I can accomplish this?

Thanks!
 
Matt said:
Hello! I am building a report based off of a query that only uses three
fields, a create time a user id and an incident number. I created a
report that will display these based on grouping by month on the create
time field. Using the =[RecordCount] through the control source field,
the report will display the total number of incidents for that month.

However, the report also listed each and every incident for the month
and then the total. Since I'm working with many, many records, I'd like
the report to just display the month and the total number of incidents
for the month (rather than listing each and every one). Is there any
way I can accomplish this?
Click to expand...


Rather than trying to munge the report to do this, you
should create a query that returns the desired data
aggregations (Count, Sum, etc). E.g.

SELECT Month(incidentdate) As MonthNum,
UserID,
Count(*) As IncidentByUser
FROM table
WHERE . . .
GROUP BY Month(incidentdate), UserID

The total number of incidents for the month can then
calculated in the month group footer section by using a text
box with the expression =Sum(IncidentByUser)
 
Back
Top