Sort a report descending by aggregate calc rather than field

[Marcus Morris]

I have a three-table query that feeds a report to show which (UK) schools
offer which (UK) courses, using multiple selection criteria supplied by a
form. For example, select courses A, C, G and K and the report displays all
schools that offer at least one of those courses. The aggregate Count
function reports how many of the selected courses each school offers. So far
so good. However, I need to order the report so that the schools with the
highest hit-rate (best match) appear at the top of the report. You can't
group/sort on an aggregate because it doesn't get calculated until the report
is underway, by which time it's too late! Please help!

 

[Allen Browne]

Yes, you are correct: Access cannot sort the report by a total that is not
known until after it runs the report. That means you have to supply the
totals to the report, in its RecordSource.

In a typical structure, you would have these tables:
- a School table (one record per school), with a SchoolID primary key;
- a Course table (one record per course), with a CourseID primary key;
- a SchoolCourse table (one record for each course at each school) with
fields:
SchoolID relates to School.SchoolID
CourseID relates to Course.CourseID
- other tables.

If so, you could create a query that gives you the count of courses per
school, and add this query to your orignal one. You have now supplied the
count to your report, so you can sort on it.

Example steps:

1. Create a new query, based on the SchoolCourse table.

2. Depress the Total button on the query design toolbar.
Access adds a Total row to the grid.

3. Add the SchoolID field to the grid.
Accept Group By under this field.

4. Add the CourseID field to the grid.
In the Total row under this field, choose Count.

5. Test the query, to verify that the CountOfCourseID gives the right count
per school. Save. Close.

6. Open your original query, and add the one you just created as a source
"table".

7. If you do not see a line joining SchoolID in this new query to SchoolID
in your School table, drag the field from one table and drop it onto the
matching field in the other table (in the upper pane of query design.) If
you see other lines joining this new "table" to others, you probably want to
delete those lines.

8. Drag the CountOfCourseID field into the output grid of the query.

9. Test the query, to confirm that it is showing the correct count on every
row. Save. Close.

10. Open your report in design view.

11. Open the Sorting'n'Grouping box (View menu.)

12. Insert the CountOfCourseID field to the top row of this dialog.

 

[Marshall Barton]

I have a three-table query that feeds a report to show which (UK) schools
offer which (UK) courses, using multiple selection criteria supplied by a
form. For example, select courses A, C, G and K and the report displays all
schools that offer at least one of those courses. The aggregate Count
function reports how many of the selected courses each school offers. So far
so good. However, I need to order the report so that the schools with the
highest hit-rate (best match) appear at the top of the report. You can't
group/sort on an aggregate because it doesn't get calculated until the report
is underway, by which time it's too late! Please help!

Right, the aggregate will have to calculated in the report's
record source query. I suggest for the sake of your sanity
in applying the criteria that you create a new query based
on your existing query and use a subquery to calculate the
aggregate:

SELECT Q.*,
(SELECT Count(*)
FROM yourquery As X
WHERE X.schoolID = Q.schoolID
) As CountCourses
FROM yourquery As Q

 

[Allen Browne]

Marsh, when I use a subquery like that, I usually get a "Multi-level
group-by error" if I try to sort by it in the report.

Any suggestions on when it's safe to use the subquery?

 

[Marshall Barton]

Marsh, when I use a subquery like that, I usually get a "Multi-level
group-by error" if I try to sort by it in the report.

Any suggestions on when it's safe to use the subquery?

You're right Allen. I guess I thought I was in the queries
group and didn't think of that issue when I posted.

The error comes from Access's internal report query that
deals with report Grouping and/or Aggregate functions. It
can be avoided by using a ridiculously stupid addition to
the query:
SELECT Q.*,
(SELECT . . .
FROM . . .
UNION ALL
SELECT Q.*, 0 FROM . . . WHERE False

Another way to avoid that error is to replace the subquery
with the equivalent Domain Aggregate. But this simple
modification comes with a significant speed penalty.

In the context of a non-trivial report, your approach is far
superior to any of my alternatives. But I think you may
have to base the aggragate query on Marcus' original query
(instead of the table) in order to apply all his criteria to
the aggregation.