You could probably write a script that would find the students for that grade level, sort by gender-behavior-acedemic, then go through each student record and assign them to alternate classes. It may not work perfectly and need adjustment, but it should put you in the ball park.
And you might consider putting all students in a single table with a field that identifies their grade level. That way you can advance students to the next grade level by incrementing this field instead of by moving their records into a different table. This approach will not keep you from working with all students of a given grade-level such as to balance class lists.
Exactly how you would do all that depends on the structure of your database. An elementary school database system might have tables like this:
Students::TeacherID = Teachers::TeacherID
Students::classID = Classes::ClassID
One or the other relationship can work for this depending on whether you want to assign students to a class or a teacher. (With team teaching and teachers on maternity leave for part of the year, linking to a class record instead of a teacher may be more flexible.)
If students need to be assigned to more than one class--such as a middle, jr high or high school, then a more complex table structure is needed so that students can be assigned to multiple classes.
Thank you for the responses. I really appreciate the insight. The student db is straight forward. Name, Gender, Academic, Behavioral. I want the script to automatically place students in a class for the next year - future teacher not relevant - just a class.
In which case you'd use the class based relationship:
Students::ClassID = Classes::ClassID
You'd create your records for each class in the Classes table and then use your script to work through the students records.
One general outline of the process would go like this:
The script would:
Perform a find to pull up all student records for a specified grade level. If this is K-12, you could set this up with a loop that finds K students the first time, grade 1 the second time and so forth.... You may want to include gender in this find so that you'd work with all Boys of a given grade level then work with all girls of the same grade level.
Sort the records by your criteria. That criteria is what you'd have to figure out here. If this were a baseball team, you'd sort by a "batting average" or some such statistic, what you'd use in your case depends both on the fields you've set up: Gender, Academic, Behavioral and the priorty you'd assign to each. (Is a student's behavioral rating more important than his/her academic rating?).
Then your script would pull up the Classes records for that grade level and then loop through the found sets in both tables so that you assign students much like you would deal cards (students) from a deck to a set of card players (Classes). The sort order combined with this "deal" establishes an approximate "balance" to the student distribution in each class. Using the "baseball" analogy, if you had 5 teams, they'd each get one of the top 5 players, then one of the next 5 and so forth to achieve approximate parity between the teams.
As a parent of grade school students, I hope that such a routine is only being used to create a "rough" draft of each class list. Getting a good "mix" for each class could be a very subjective process relying on details not easily quantified in a database--such as putting a student known for bullying others in the same class as the student he previously victimized the most often the previous year...
Philmod thank you for the information and suggstions. Yes this would be a first attempt to help create the framework for placement...then comes the more refined work of remediating the exceptions. thanks again.