11 Replies Latest reply on Jul 19, 2014 7:07 PM by JosephFalduti

    Multiple class selections per record

    JosephFalduti

      Title

      Multiple class selections per record

      Post

           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.

      Screen_Shot_2014-07-07_at_5.41.08_PM.png

        • 1. Re: Multiple class selections per record
          philmodjunk

               It would appear that you've set up a typical many to many relationship with other tables also linked in:

               Students----<studentClasses>-----Classes>------Instructors

               But you also have something called a "session" it it would appear that you really need a table for sessions in addition to what you have thus far:

               Students----<StudentSession>-------Sessions>------Classes>-------Instructors

               You would create one record for each session offered where not only is the date and time of the session recorded, but the class taught during that session. You might even find it better to link Instructor to Sessions as different sessions of the same class are likely to be taught be different instructors on occasion.

               With this structure, StudentSession can be used to produce the class rosters for each session.

          • 2. Re: Multiple class selections per record
            JosephFalduti

                 Hi Phil,

                 I thought this was the correct way to set up something like this to avoid a many to many relationship.  

                 The session means which week they are coming: Session 1, Session 2, Session 3, Session 4...  It doesn't mean a time or morning/afternoon session.  So each Session would have five days.

                 We don't separate students by sessions.  The Students table is our main table.  We typically use the Sessions from a Value List in a variety of ways.  I used it in the Classes table as a way to categorize and identify them, as the same class could be taught by the same instructor over multiple sessions.
                  
                 However, I still don't know how to solve the problem that each record would have multiple class choices that need to be connected to the list of classes.  For example:
                 Susie Student --------<Susie Student - Session Two>----------Session Two>---------Monday Morning, Monday Afternoon, Tuesday Morning, Tuesday Afternoon...>----------Instructors
                  
                 Unless you meant something else for Sessions and this doesn't work.
            • 3. Re: Multiple class selections per record
              philmodjunk
                   

                        We don't separate students by sessions.

                   Appears to contradict:

                   

                        I DO need the "scheduled" class and the afternoon assigned class to be linked, because we will use that to generate class lists.

                   And it seems strange to me that you wouldn't separate them by session as usually a key part of scheduling student class requests is to avoid assigning too many students to the same class at the same time.

                   What I envisioned is that each Session record records a particular date and/or time frame for which a given class is set up to be available. Thus, by assigning a student to a session, you also assign them to the class specified for that session. Thus a "session" in my response isn't precisely the same thing as what you describe as a "session" as it appears to me that you think of a "session" as a specific date range.

                   And keep in mind that my set of relationships and your original set are not mutually exclusive if you use different groups of Table Occurrences to set up the relationships. Thus, you can use your original setup to specify the class requests for each student and the set I recommended in order to take those requested classes and schedule them by assigning them to specific sessions.

              • 4. Re: Multiple class selections per record
                JosephFalduti

                     So If I'm understanding you correctly, you're saying create Sessions tables in the following way:

                     Monday AM, Monday PM, Tuesday AM, Tuesday PM, etc.

                     Then link the appropriate classes from the Classes table into each appropriate Sessions table.  Then use the key from each Session table to link the class to the correct field in the StudentsClasses table?

                     Am I understanding you correctly?

                     And to keep this a little more tidy, I can use a second Table occurrence of the Classes table to prevent a mess in the Relationships tab.

                     This sounds doable.  However, this would mean that after I input classes in to the main Classes table, I would then have to download the keys for each session and reupload them into each Session table, no?  There's no easier way to do this?

                     Hopefully I'm understanding what you're suggesting.

                • 5. Re: Multiple class selections per record
                  philmodjunk

                       You seem to be understanding my suggestion. but

                       

                            However, this would mean that after I input classes in to the main Classes table, I would then have to download the keys for each session and reupload them into each Session table, no?

                       I'm not sure that I follow that. Specifying which classes are available for a given session should be part of what you need to do anyway, and in many cases might be as simply done as clicking a button to perform a script to do this.

                  • 6. Re: Multiple class selections per record
                    JosephFalduti

                         Yes it is- the class day is specified in a field during upload and so is the time/type (elective or major)

                         So perform a script that's something like:

                         If Day=Monday and Time=Morning the copy ClassPK (the key field) to Monday AM table?

                    • 7. Re: Multiple class selections per record
                      philmodjunk

                           Once again, I'm not sure that I follow what you want to do. Keep in mind that I am not familiar with exactly how you need to manage this process, only in general.

                           Do you mean that from a list of selected classes for each student, run a script to assign them to specific sessions?

                           That's what I had in mind, but can't quite figure how "day = Monday and time = Morning" figure into that process.

                           Or are you talking about taking a set of records in classes and generating the needed set of sessions records?

                      • 8. Re: Multiple class selections per record
                        JosephFalduti

                             OK,

                             In terms of linking the tables, I am picturing this now:

                             Students---<StudentClasses>----MondayAMClasses>---AllClasses>------Instructors

                                                                               >----MondayPMClasses>---

                                                                               >---TuesdayAMClasses>---

                                                                              >----TuesdayPMClasses>---

                             This would enable me to get a unique ID into each AM and PM field in the image above.  

                              

                             Every record in the AllClasses table already has a field that denotes the Day as well as Time, as well as which week the class is occurring in.  So the point I was making was that It would be easy to script something to look for records that occur within a given week and on a specific day and time of day, and copy their key fields into the appropriate "Session" table (i.e. the MondayAM, MondayPM, etc. table).

                             This should enable me to get a unique key for each class "Session" that I could populate  into the appropriate field in each record in the StudentClasses table.  So one record in the StudentClasses table would look like this:

                             StudentID      MondayAMID      MondayPMID      TuesdayAMID     TuesdayPMID ...

                             Is this what you had in mind? 

                        • 9. Re: Multiple class selections per record
                          JosephFalduti

                               Apparently in order to do this I have to make a table occurrence of AllClasses and Instructors for each Session I create.  Why bother with the AllClasses table at all then? Why not just upload the classes into the individual session tables to begin with?

                          • 10. Re: Multiple class selections per record
                            philmodjunk

                                 I do not see the need for:

                                 MondayAMClasses

                                 MondayPMClasses

                                 TuesdayAMClasses

                                 TuesdayPMClasses

                                 What problem are you trying to solve with all of these tables or Tutorial: What are Table Occurrences??

                            • 11. Re: Multiple class selections per record
                              JosephFalduti

                                   Phil,

                                   The problem I'm trying to solve is that if each record has 6 foreign key fields (1 for each class), if I only use the corresponding key from the AllClasses table in each of the six Class ID fields, it does not show the information for EACH class (like class title, instructor, etc), ONLY the first instance of a class.  But since I want these all to populate with the class information I need them to come from different tables.