Struggling to create correct relationships in MS Access

Basically: an award(course) has many units, which can be either optional or core(mandatory), depending on the award. So for example: the unit 'Advanced Software Engineering' maybe a core unit for the award 'Software Engineering BSc' but only an optional unit for the course 'Web Technology BSc'. I've used flags for that purpose.

A student is enrolled on an award so I need to get a complete list of core and optional 开发者_运维百科units (bearing in mind that a student chooses 1 out of many possible optional units).

Also, these units have events, e.g, a lecture, workshop or seminar, etc. and those events have sessions or instances of events where students enrolled on that particular unit are required to attend, and those attendances are stored in a separate table to form a register.

So I need a hierarchy of expanding the tables something like this I guess:

• Awards -> Students -> Units -> Sessions -> Attendances

Any help with this would be appreciated... It's blowing my mind and I'm really close to going insane! My tutor didn't spot I'd got it wrong when I showed my original data model to him and it's due in next week! Thank you :D

---

At a first glance, I do not think students need to be related to Awards or Units, but to AwardUnits. Perhaps:

 Awards  Units
   |       |
  AwardsUnits -- AwardsUnitsSessions
    ( AwardID            |
    ( Mandatory          ---- AwardsUnitsSessionsStudents (ie Attendances)
        |                         |
 AwardUnitsStudents--Students-----|

When a student is created, an Award is selected and all mandatory units from AwardUnits are added to AwardUnitsStudents and a list of optional units are presented to the user so that the student choices can be added.

EDIT You might like to consider a persons table that contains all common person details (in quite a few cases, the address table should be separate) with additional tables to define extra information for staff, student, tutor, etc. This is a common enough style, and can make life easier, especially when one person has more than one role.