Teacher Subject Allotment Database

[grieve_physics]

Hi All,

Relatively new to access and new to the forums. (although I am high end Excel VBA user),


I am trying to turn a clunky excel database and update it to access 2013. The database will be used every year at the school to assign teachers to the classes & subjects they teach.

Current Table Setup, has caused me issues, Although i can link classes to subjects, the subject is shared for all classes. For example, If i assign a teacher to a 7B Maths class, it places that teacher for all year 7 maths classes. I believe it is because of how i set up my tables.

[tblClasses]
YearLevel (i.e. 7,8,9,10)
Classes (i.e. 7A, 7B, 7C, 8A, 8B, etc...)

[tblSubjects]
Subject (i.e. Maths, Science, English, etc.)
YearLevel (i.e. 7,8,9,10,11,12)
NumofPeriods (i.e. 2,6,7,etc.)
StaffCode (i.e. GRI, STA, RUS, etc)

[tblStaffPrefences]
StaffCode (i.e. GRI, STA, RUS, etc.)
StaffName (i.e. John, Susan, etc.)
TotalNumberPeriodsCanTake (i.e. 24, 28, 30, etc)


At the end of the day, i need a simple updateable list of classes, subjects, teachers. That are able to be grouped together. (i.e. 7B Maths GRI, 7C Maths STA, 7D Maths RUS)

Unable to find a solution on the internet so thought i would ask the experts
Thankyou in advance, any pointers would be great.

[June7]

Why is YearLevel in tblClasses? Looks like just room numbers.
tblSubjects needs a unique ID field - autonumber type will serve.
Then you need another table, at least. A junction table.

tblStaffAssignments
ClassRm
SubjectID
StaffID

[orange]

I agree with June re junction table eg, StaffIsAssignedClass.

Some of these can get tricky when you break out Course and Class or ClassSession
For example, Course Algebra300; ClassSession(s) Monday 10-11AM Room 21; Wed 2-3PM Room 50; Fri 11-12Noon Room21
I don't see that complexity here, but just pointing it out for reference.

If TotalNumberPeriodsCanTake is allotted on some preference hierarchy, you'll need logic to do that allocation.
You may need to know StaffAvailability for that allocation.

You may get some ideas from this free data model.