count distinct values despite multiple appearances

[Amanda Redmond-Neal]

i have a query with the following fields (among others):
school district, school, date (of a presentation indicated
by each record), and number of students (having attended
each presentation).
since there are multiple presentations at each school, and
multiple schools in each district, both the "school"
and "school district" fields have multiple identical
values.
the report i am running from this query is grouped by
school district and shows the number of presentations
(simple =Count(*) expression) and the number of students
(simple =Sum([number of students]) expression).
WHAT I NEED HELP TO DO is insert a control in the report
which will count the number of schools in each district --
only once for each value for "school" no matter how many
individual records it appears in.
i'm asking this question in the query newsgroup because i
suspect the answer lies at that level. hope i've
explained it clearly enough. thanks in advance for
replies.

 

[Jeff Boyce]

Amanda

It sounds like you want your report to include a count of the number of
schools per district, in that district's "section" of the report.

One possibility would be to group by district in your report, and add a new,
unbound control. Set the control source of that control to something like:
=DCount("YourTable","YourSchoolName", "YourSchoolDistrictFieldName" =
[SchoolDistrictFieldName])
(this is pure aircode ... actual syntax may vary, and I haven't tested this
approach, so no guarantees).

Another possibility would be to write a small totals query that gets ALL the
districts' counts-of-schools, then build a subreport on that query. Link
the subreport to your main report, in the District group, on the district.
As each district group is formatted, the subreport will get/display the
totals/count. I've successfully used this approach before.

Good luck

Jeff Boyce
<Access MVP>

 

[John Spencer (MVP)]

Another possible method in a report is to group by School and add a control to
the Group header.

Name: txtCountThem
Control Source: =1
Running Sum: Over All
Visible: No

Then in the report's footer (not the page footer) add another control

Name: txtCountSchools
Control source: =txtCountThem
Visible: Yes

I've used that technique in the past