Sorting a report on a calculated field

[Derrick Pasley]

I'm trying to sort a report based on a calculated "COUNT" field. I have a
field called "school", and a second field called "category". Each "school"
has multiple "categories", and my intent is to count the total number of
categories for each school. At the same time when displaying the data in the
report, I want to sort the results based on that calculated field, and I want
to display the data so it also shows the schools' breakdown by category. I
used a query to count the data, but I was unable to figure out how to create
a query to allow me to display the breakdown by categories. If anyone has
any suggestions on how to do this, I would be most appreciative.

 

[Duane Hookom]

If I understand correctly you don't have a CountOfCategories in your report's
record source. If this is true, you need to add one. This can be done by
creating a totals query (qtotSchoolCat) that groups by School and Category.
Then create another query like:

SELECT School, Count(Category) as CatCount
FROM qtotSchoolCat
GROUP BY School;

Then, add this last totals query to your report's record source and join the
School fields.