ansaurus

Question

Struggling to create correct relationships in MS Access

Answer 1

A:

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.

Remou 2010-04-20 07:48:01

Thank you, @Remou!So, in AwardsUnitsSessionsStudents I'd have the IDs for awards, units, sessions and that same row would be repeated for how ever many students there are required to attend an instance of a session?and in AwardsUnitsStudents there'd be the ID of Awards and that's repeated for however many Units there are for that award, and that's repeated for however many students there are in that unit?just checking I've understood your model =]

Yandawl 2010-04-20 16:49:46

Yes for AwardsUnitsStudents, however, for AwardsUnitsSessionsStudents you have a few possibilities. You could enter only students that showed up, you could enter all rows for required sessions and only students that showed up for optional sessions, and so on.

Remou 2010-04-20 17:24:20

@Remou Hmm... I can't pull the AwardID from AwardsUnits to AwardsUnitsStudents though can I because it's not a key in AwardsUnits?Also, in my interface I want to allow the user to input an event, such as a lecture or a workshop, etc... and then the system automatically adds the sessions for those events on a weekly bases to AwardsUnitsStudents or whatever... The whole point of the system is to allow users to print off a list of all students who are supposed to attend and specify after that session has taken place whether they did or not.

Yandawl 2010-04-22 22:12:54

So wouldn't it be better to have a separate Sessions table that lists all sessions in a unit so that we can list in another table the attendances that pulls the students taking that unit? I'm just haven't got that to work before =[

Yandawl 2010-04-22 22:14:01

Another problem I have is that a student has to study for 3 years and they make their option choices at the end of level 1 and level 2 so I need to find some way of pulling a list of all possible choices and mandatory units and only specify option choices from that list when appropriate

Yandawl 2010-04-22 22:43:33

Your original question was about relationships and these notes seem to expand on the original question. Certainly you should have a sessions table, the above is a sketch of the area around awards, not an exhaustive list of tables, furthermore, AwardsUnitsSessions implies such a table, note that other tables shown in your outline have also been omitted from the sketch, for the sake of clarity.

Remou 2010-04-23 08:09:17

If the interface is not working for you, I suspect some misunderstanding may have crept in. Stackoverflow is a knowledge base, if you wish to move to a forum discussion, I can be found at lessthandot.com.

Remou 2010-04-23 08:17:25

ansaurus

tags:

views:

answers:

Struggling to create correct relationships in MS Access

related questions