Timetabling of ESOL Classes

[Alan L]

Hi
I’m trying to help my wife who is a teacher of English as Second Language (ESOL) teacher.
After initial English language testing, she places her students in ESOL Groups (Group 1, Group 2 …) of between 1-5 members, depending on their level of English.
She then produces an ESOL timetable to bring her student out of their Homeroom classes in their ESOL Group (Group 1, Group 2 …) for ESOL lessons. Students can be placed in 1 to 4 groups (ESOL 1^st Group, ESOL 2^nd Group…), depending on their level of English. This is done on a large hardcopy of the weekly timetable with lots and lots of pieces of paper with student’s names on them.

Problem:
I’ve set up the attached spreadsheet to show the different layers of information that my wife needs to consider when developing the timetable for her ESOL Groups.
Tab 1: ESOL Student Data (ID, First Name, Last Name, Preferred Name, Agent, Date of Birth, Gender, Year Level, Homeroom Number, Homeroom Teacher, House)
Tab 2: ESOL Group Data (Group 1, Group 2, Group 3 …). Students can be in up to four different groups.
Tab 3: PE Data (Timetable Cell, House, Students)
Tab 4: Academy Data (Timetable Cell, Academy, Students)
Tab 5: Timetable Template (Timetable Cells)
Tab 6: House & Tech Timetable – Classes
Tab 7: PE Timetable – Student & House
Tab 8: Academy Timetable – Classes
Tab 9: Academy Timetable – Students
Tab 10: Specials Timetable – Classes (Assembly)
Tab 11: Timetable Solution

Key points she needs to consider:

1. Regular classes operate on a timetable of four blocks per day, Monday to Friday (B2 to F4). See Tab 5.
2. Blocks from Monday to Thursday are assigned to each of six Houses; Batten, Cooper, Hillary, Ngata, Sheppard and Upton. See Tab 6.
3. Students attend their Technology (Tech) classes with their Homeroom Class as a House. See Tab 1 and Tab 6.
4. Students must attend Physical Education with their homeroom class. See Tab 7.
5. Some ESOL students get accepted into one of the following Academies (Year 7 History, Year 8 History, Year 7 Science, Engineering, Robotics, Year 7 Writing, Year 8 Writing, Year 7 Mathematics, Year 8 Science, Sports, ICT, Philosophy, Technology, Mathematics, Kapa Haka), which operate on Friday during certain blocks (F2, F3 and F4).
6. All students attend Assembly Block 1 (B2) each Monday. See Tab 10.
7. My wife needs two free Blocks with no ESOL Groups for her planning.
8. My wife does not work during Block 4 (F4) on Friday.
9. I’ve produced a Timetable Solution (see Tab 11) of what I want the outcome to look like.

Solution:
I want to help my wife by producing a programme using Access database that will enable her to enter the appropriate data (Student’s Name, Reading Group(s), House/Technology Block, Physical Education classes, Academies and Special Blocks) into a database Form and it would provide possibilities of Block combinations that work for all her students.
I have very little knowledge of databases and so have been watching lots of You Tube clips build up my basic level of knowledge and skills.



I've attached a screen shot of the Table I've created in Access.

Can someone please help me? I will need someone who is willing to help me over the next few weeks/months by answering my questions and providing possible solutions to problems I’ll encounter.

[Alan L]

Sorry, I've realised that I didn't include the Timetable Solution in the 11th tab on the spreadsheet. I will do this and post it as soon as possible.