creating report with multiple criteria

  • Thread starter Thread starter Roxann
  • Start date Start date
R

Roxann

Please help!! I have a simple query, but am having trouble
sorting it into the form I need for my report.

I need the report sorted by school name # of students
attending, but it needs to count every student only once
based on the most recent date in the DateEnrolled field.

I have just three fields:
"Student#", "SchoolName", "DateEnrolled"

A sample query might say:

Student #1 Washington Elementary 09/28/2003
Student #1 State Elementary 09/15/2003
Student #1 Greenwood Elementary 08/15/2003

Student #2 Washington Elementary 09/28/2003
Student #2 Greenwood Elementary 09/12/2003
Student #2 State Elementary 08/27/2003

Student #3 Greenwood Elementary 09/28/2003
Student #3 State Elementary 08/21/2003
Student #3 Washington Elementary 08/15/2003

I need a report that says:
Washington Elementary = 2
GreenWood Elementary = 1
 
Click on the "Sorting and Grouping" button on the Report
Design bar and set up your sorting criteria.
 
If I understand your description, you need to create a Totals Query and use
that as the RecordSource of your Report. It's in the Totals Query that you'd
count the schools.

In Query Design View, create a standard SELECT query to show Student,
School, and School again. Then View | Totals, and put Group By under Student
and the first occurrence of School, and Count under the second occurrence of
School.

Larry Linson
Microsoft Access MVP
 
Back
Top