Count query in report field

[Jaded in Cali]

In an educational survey report, I am listing the results in the detail area
for each section of a course responding to the survey. In the Course
section, I have summary fields for the number of sections reporting and the
total number of students this covers. These =sum(...) fields are easy

I also need to query the table, tblSections, where the Section_ID,
Course_ID, period, and teacher are stored for the total number of sections in
the course.

I have tried several variations on

=Count(IIf(tables!tblSection!Course_ID=[Course_ID],tblSection!Section_ID,0))

but I get parameter requests for both tables!tblSection!Course_ID and
tblSection!Section_ID.

I know that Reports are finicky about some formulas and expressions. Is
there another way I can do this?

 

[Duane Hookom]

Your control source expressions must include fields from the report's record
source. You can't use an expression like yours or a select statement. I
would probably create a query like:

SELECT Course_ID, Count(*) As NumSections
FROM tblSection
GROUP BY Course_ID;

Then include this totals query in your report's record source and JOIN the
Course_ID fields.

You could also use DCount() in your control source.