Finding data from a summary field .

[William Bell]

I have an enrollment database for classes and I need to inform people if they are in the class or on the waiting list for the class.

As classes vary in size each class has a maximum number field and I have set up a summary field in the student/class join table to count the number of enrollments when sorted by class name. A third field then calculates if the summary is greater than the maximum number field. If not it gives "Enrolled" and if so it gives "Wait List".

The problem arises when I want to find the records of students who are on the "Wait List". As summary fields are unstored I cannot find them.

Is there a work around to make the data stored.

[comment]

You could define a self-join relationship of the Enrollments table as:

Enrollments::ClassID = Enrollments 2::ClassID

AND

Enrollments::Date ≥ Enrollments 2::Date

(assuming the priority is date-based).

Then a calculation field in Enrollments =

Count ( Enrollments 2::ClassID ) > Classes::Size

can return true/false waiting status.

This is still unstored, but unlike a summary field it doesn't depend on a found set, therefore it's searchable.

[William Bell]

Thanks a million it works.

Could I possibly pick your brains with a further question.

There is also the possibility of insufficient enrollment in a class, in which case it will not run. If the minimum number is 6, I have used the count summary field for a calculation that returns "Insufficient Enrollment" if there are 5 or less students enrolled.

This works up to a point, but the problem is that it does not change after the minimum number is exceeded.

If you are busy I post this to the forum, and I promise I won't ask any more questions.

[comment]

You don't want to use summary fields for any of this, since they summarize the found set. To get a "Insufficient Enrollment" flag, you should be using a calculation field in the Classes table =

Count ( Enrollments::ClassID ) < Size

[William Bell]

Thanks again.

This is a much more sensible approach. I got the idea of using summary fields from a book, but I might have misunderstood what they were getting at.

[comment]

Well, it IS possible to use a summary field to summarize the related set, too (see the attached). This option has been continually improved since version 7 and is now quite usable. But since you need this in a calculation anyway, there's little advantage to it.

RelatedSummary.fp7.zip