Student schedule database, table structure help.

Hello to All!

(This might look like a long post, but most of it is just background information)
:)

I am setting up a Student Schedule database in Microsoft Access (The RDMS should be irrelevant here; my question is “architectural”).
Certain linked tables are already set up and have data in them. I need to add a “Schedule” table.

Here are existing tables:

STUDENT:
id (PK, Char)
last (Char) Last name
first (Char) First name
dob (Date) Date of Birth
exit_date (Date)

STPROG ...Student's Programs
datetimeid (PK, Char)
id (FK, Char) Student ID
st_program (Char) …Students Program name
st_start (Date) …(Students Program Start Date)
sch_year (Char) …School year designation

CONTACTS (Student emergency Contacts)
contact_id (PK, Char)
id (FK, Char) Student ID
addr1 (Char)
city (Char)
home_phone (Char)
f_name (Char) …the Contact’s Father and Mother’s first name
role (Char) …the Contacts “Role” (1P=First Parent, 2P=Second Parent, GP=GrandParent …ect)

I use a Criteria Form to supply the needed criteria to the query.
Here is the query:

SELECT student.id, student.last, student.first, contacts.addr1, contacts.city, contacts.home_phone, Len([Home_Phone]) AS PhoneLen, Trim(IIf(Right([Home_Phone],3)=" ","631" & [Home_Phone],[Home_Phone])) AS MaskPhone, Len([MaskPhone]) AS MaskPhoneLen, contacts.f_name, student.dob, IIf(DateDiff("m",[dob],Date())>35,Format((Date()-[dob])/365.25,"Fixed") & " yr",DateDiff("m",[dob],Date()) & " mo.") AS Age, DateDiff("m",[dob],Date()) AS AgeInMonths, student.exit_date, stprog.sch_year, stprog.st_start, stprog.st_end, contacts.role, stprog.st_program
FROM (student INNER JOIN stprog ON student.id = stprog.id) INNER JOIN contacts ON stprog.id = contacts.id
WHERE (((student.exit_date) Is Null) AND ((stprog.st_start) Between [Forms]![frmSelectStudentSchedules]![txtStart] And [Forms]![frmSelectStudentSchedules]![txtEnd]) AND ((contacts.role)="1P") AND ((stprog.st_program) Like [Forms]![frmSelectStudentSchedules]![cboProgram] & "*"));

When run, it will return something like this:

id,last,first,addr1,city,home_phone,PhoneLen,MaskPhone,MaskPhoneLen,f_name,dob,Age,AgeInMonths,exit_date,sch_year,st_start,st_end,role,st_program
124947,Trocchio,Jessica,7 Broadway,Holtsville,6312892901,10,6312892901,10,Tracy/Phil,38528,15 mo.,15,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007
124967,Keller,Riley,15 Old Schoolhouse Road,Center Moriches,9092423,10,6319092423,10,Riley,38597,12 mo.,12,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007
124969,Siniski,Nathaniel,24 Bailey Road,Middle Island,7759196,10,6317759196,10,Laura/Steve,38466,17 mo.,17,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007
125093,Harry,Sabra,13 East End Avenue,East Quogue,6316539222,10,6316539222,10,Tabitha/Bruce,38720,8 mo.,8,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007
125097,Morris,Emma,116 Belleview Avenue,Center Moriches,6319093364,10,6319093364,10,Kimberly/Edward,38629,11 mo.,11,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007
125098,Morris,Riley,116 Belleview Avenue,Center Moriches,9093364,10,6319093364,10,Edward/Kimberly,38629,11 mo.,11,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007
125113,Giannetto,Justin,21 Logan Place,Mastic,2817731,10,6312817731,10,Stephanie/Anthony,38608,12 mo.,12,,2006-2007,38961,39255,1P,CCAF06NISNISCB2006-2007

This would represent, let’s say, Infants (st_program: Like cca%)  for school year 2006-2007.
Now, for these students I need to add their schedule. My proposed Schedule table looks something like this:

tblSchedules
ScheduleID (PK, Num)
MonStart
MonEnd
TueStart
TueEnd
…ect until Friday.

Here is how I envision it will work:
I will use the above Query to get a list of all Students enrolled in a certain Semester. (Actually based on st_start) Then fill out the schedules for each student.

But I need help with my table setup and relationships.

As you can probably deduce, one student can be enrolled in many semesters (ex., School Year 2006-2007 Summer 2007, School Year 2007-2008…ect)
One Student can also have many schedules. (ex. One Student can have a schedule for each Semester they are enrolled.)

So that begs the question, would the STUDENT table be linked to the “Schedule” table (One student, many schedules) OR would the Student table be linked to a “Semester/Schedule” junction table that would represent One student in many Semesters/One Semester many Schedules? (Or would some other arrangement be needed?)

My ultimate goal is to be able to select the two criteria from the criteria selection form, run the query and, using a form based on the query, fills in the schedules. (Main form/subform perhaps?)
(I could then also create a report based on the query…Two years from now, I could go back and see what a students schedule was…. ect)

I hope I have provided enough information here, if not I will be happy to provide more.

Thanks!
Start Free Trial