count only distinct values despite multiple appearances

  • Thread starter Thread starter Amanda Redmond-Neal
  • Start date Start date
A

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.
 
Add a new level of grouping header and footer for the school. Thus under
school District you could have one or multiple schools listed. Under each
school listed you would get a count for the number of schools.

Example:
District 12
Azalea Park Elementary
6/1/03 Security in the classroom
7/2/03 Exceptional Teachers
Subtotal: 2
Andrew Jackson Middle School
6/1/03 Instructional Basics
7/2/03 Exceptional Teachers
Subtotal: 2
District 20
Colonial High School
8/1/03 blah blah......
Subtotal: 1
District .......

At the very end you can give a total for the entire school grouping. You
could also not give a subtotal for each school grouping, thus elimininating
the footer group on School.

Note: Word of caution - Date should be preceded with a subject, such as
'PresentationDate'. Date is a reserved/keyword in Access and will cause
future problems. You should be able to change the field without any major
repercussions. However, before changing any data type in any database make a
copy of the db. Change the name and then be sure to update any queries,
forms, and reports, code that uses the old field name.

If the database is shared among multiple users, you will need to wait until
everyone is out of the db and open it into exclusive mode before making any
field name changes.




You get the picture
 
Back
Top