I have a database of ESL students that keeps track of the courses they register for. My 'Registrations' table holds multiple registrations per session for each student. Each student has a unique ID as does the course they register for. Student IDs are typed in and their personal info is brought in from the 'Student' table. A one-to-many setup. The same for the courses.

I'm trying to get a report to count the number of students registered for less than 4 courses in a given session. With multiple instances of each student in the table how do I go about calculating this?

Thanks to everyone with advice.

I guess the real question is how to I set up a script that does the following:

identifies all duplicate student IDs

only counts one of the duplicates

then counts the courses the student is enrolled in for that session/yr

then counts and reports only those students taking less than 4 courses

From your registration table:

If you have a self joining (Multicriteria) relationship based off studentID and Session, you can you a Count (SelfJoinRelationship::ClassID) to determine how many classes for each student for that session.

Then you would need a global field or something for users to enter what the current session is.

Then in your script, you can do a simple find

Find []

Set Field [ Registration::Session; Globals::gCurrentSession ]

Set Field [ Registration::cCountClasses; "<4" ]

Perform Find []

Go To Related Record [show only related records; Match found set; From table: "students"; Using layout: "studentlayout" (students)]

I havent checked the syntax. But basically, you are finding all records that match the value for what you put in the global current session field and that have less than 4 associated courses for those sessions. Then it uses go to related records to show you all the students that match the found set in the registration table.