Multiple class selections per record
I have been wracking my brain for weeks trying to figure out how to make this work and I'm so fried and desperate.
I have a table full of students (called "Students"). I have another table with a list of classes (called "Classes"), which is connected to another table of instructors. The students and classes are linked by a join table called "StudentClasses".
We have five weeklong sessions. The students pick their first, second, and third choice classes for each session. We then assign them their scheduled class. Student choices go into a spreadsheet on Google that looks like this:
Student Name ID Monday 1st Choice Mon 2nd Choice Monday 3rd Choice Tues 1st Choice Tuesday 2nd...
I then have a portal set up in a tab in each student's file. I have spaces for each choice: 1st, 2nd, 3rd, plus a space for SCHEDULED which is what we assign them to. There is also another field for their afternoon class which is pre-assigned to them.
I don't need the student's choices to be linked to the classes because that's just for our records. I DO need the "scheduled" class and the afternoon assigned class to be linked, because we will use that to generate class lists.
Currently each portal row houses every day. So therefore I have multiple fields that need to link to a match field in the Classes table. About 10 to be exact. But I don't think you can use multiple match fields like that, can you?
To make things more complicated, I originally wanted to have a dropdown of the classes for that given session and day in the "Scheduled" box, to make it easy to switch students to different classes without having to look up class ID numbers. I could also filter it by date if that's easier. Again I don't know if this is doable.
I do realize that I could have every day be a new record for each student. But with 150 kids per session in four sessions for five days that would be 3000 records in one shot. That's a lot of records I'd like to avoid!
I'm attaching a snapshot of what the portal looks like as I've envisioned it. If anyone has ideas I'd be ever so grateful.