In an Access report, how can I count specific records in a group?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to create a report that has the following fields: Date, Channel,
and Track. The report is grouped by Date, then Channel, and then Date again.
The first Date grouping goes by months.

The track field can only have two values, sales or technical. So
essentially what I have is a list by month, then by channel, of dates that
say sales or technical next to them.

What I want to do for each month, and each channel in the month, is count
the number of sales records, and technical records.

With the help of the office assistant in Access, I was able to count all the
records for each channel in each month, but couldn't distinguish between
sales and technical.

What the office assistant had me do was create an invisible textbox in the
details section of the report with a name of RecordCount, control source =1,
and running sum by group. Then I was to create another text box in the
Channel footer with the control source =[RecordCount].
 
Thor said:
I'm trying to create a report that has the following fields: Date, Channel,
and Track. The report is grouped by Date, then Channel, and then Date again.
The first Date grouping goes by months.

The track field can only have two values, sales or technical. So
essentially what I have is a list by month, then by channel, of dates that
say sales or technical next to them.

What I want to do for each month, and each channel in the month, is count
the number of sales records, and technical records.

With the help of the office assistant in Access, I was able to count all the
records for each channel in each month, but couldn't distinguish between
sales and technical.

What the office assistant had me do was create an invisible textbox in the
details section of the report with a name of RecordCount, control source =1,
and running sum by group. Then I was to create another text box in the
Channel footer with the control source =[RecordCount].
Click to expand...


Add a text box to the Channel group header/footer section
and use an expression like:
=Sum(IIf(track = "sales", 1, 0))

Use a similar text box to count the technical records.

Instead of the running sum approach to count all the records
in each channel, it would be simpler to use a text box with
the expression:
=Count(*)

You can use copies of those three text boxes in the month
group header/footer to get the totals for each month and yet
another set of copies in the report header/footer section to
get the grand totals for the entire report.
 
Back
Top