Now that's a challenge! Since you have to use the student and course ID's to look for entire sets of records, this won't be a simple thing to set up.
First you'll need to set up a way to document which set's of course ID's represent those required for an award. I can think of two approaches: 1) a return separated list of Course ID's in a single file, or 2) a set of related records with one course ID stored in each related record. Not sure yet which approach will be the better option here...
Then, you'll need to add a table related table that documents which awards have already been awarded to a given student.
Key question, could a student register to take the same course more than once?
A student could register for the same course multiple times.
I'm going to try to work with those ideas and see what happens. Let me know if you have any more ideas. Thanks!
The fact that (as I expected) a student can register multiple times for the same course greatly complicates this.
I had been thinking at first of using a Return Separated list of IDs as relationship match key such that the count of related registration records would tell you if an award is justified due to the unique way that a return separated list of values functions as a "match" field. But this will not work given the fact that this method will count all the times that a student has registered for the same course. If a student needed to take courses 1, 2 and 3, taking course 2 three times would count the same as taking each course once.
I think the first challenge will be to produce a set of related records that lists each course taken only once. It's a pity that you aren't using FileMaker 12 as I can conceive of a SQL query used in ExecuteSQL that might be just the ticket for resolving this one.On the other hand, there may be a way, using a join table...
I don't have time to really do much testing to figure one out at the moment, I'm neck deep in a database project of my own. Perhaps another forum participant will find that this is a challenge worth taking?
and one more question:
Criteria: A student must complete courses 1, 2, 3, 4, 5, & 6 OR courses 1, 2, 3, 4, 6, & 7 OR courses 1, 2, 3, 4, 6, & 8 OR courses 9, 6, 7, & 5 OR courses 9, 6, 7, & 8 in order to qualify for an Award.
Does this describe 1 single award or 5 different awards?
All of those options qualify for one award.
I completely understand being too busy for a tiny feature like this. Thank you for your help! I might wind up settling for a warning when a student is "one class away" from getting an Award (surprisingly that is a lot easier to do since I can use post-class exam scores and not course regitrations) ... It's not a perfect solution, but it's better than nothing for now.
Though if anyone else knows a simplier way to do this, I welcome all other ideas!
If this were "little" I'd post a quick solution. It's anything but.
I had an idea. This isn't the full solution, but may point you in the right direction.
You have these relationships:
From students, List ( Classes::ClassID ) returns a list of ClassIDs for which this student has registered.
If you had a table of records that lists the courses required for an award (we'll leave out the fact that there are multiple lists for the same award for now), linked to students, then
ValueCount ( FilterValues ( List ( Classes::ClassID ) ; List ( Award1::ClassID ) ) )
could be used to determine if a student has registered for the correct list of classes to qualify for that award.