0

I am creating a project where Teachers enter a students coursework grades and it does various reporting on that data.

It is for a course where there are many units and within those units are many assessment objectives that the students can meet to either obtain a fail, pass, merit or distinction.

In the course there are 20 units but the school chooses a selection of these which they will teach to student. For example, my school chooses Unit 1, 3, 4, 7, 15, and 20.

Below is an image of what my current database looks like

https://i.stack.imgur.com/Glb7P.png

https://i.stack.imgur.com/In7Rq.png

The problem i'm having is that access won't let me have multiple students which have have the same units. Also I want a separate table where I can have unit information such as name and description but I'm not sure how to do it?

Hopefully I've provided enough information, if not please don't hesitate to comment.

Any help would be greatly appreciated!

Many thanks, Robin

Robin L
  • 177
  • 3
  • 3
  • 12

1 Answers1

2

You need a m to n relationship. Since those are a logical concept but not a physical concept, you need an additional junction table between the students and the units, so that you have

student <---- 1 : n ---> student_unit <--- n : 1 ---> unit

Table unit
----------
UnitID (PK)
Name
Description

Table student_unit
------------------
StudentID (PK, FK)
UnitID (PK, FK)

I would also make the difference between a table describing an assessment (tlkpAssessment) and another unction table between tblUnit andtlkpAssessment. It allows you to assign the same assessment to different units. In these junction tables the ID columns are of typeNumber, Long Integerwhich matches theAuto Numbercolumn usually used for the IDs in the main tables. I also would set theirRequiredproperty toYes`.

The prefix tlkp means "Lookup Table". I usually keep all table names in singular.

enter image description here

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
  • You are a star mate, will add to your reputation if I can work out how to. I'm pretty new round here :-) – Robin L Feb 12 '13 at 20:08
  • On the left hand side of the answer, you can click the up- or down-arrows in order to upvote or downvote answers that you like or dislike and you can click the ckeck mark to mark the answer you think is the best one. – Olivier Jacot-Descombes Feb 12 '13 at 20:13
  • Although the database works I just realized I hadn't mentioned something that may have been important or I'm just not understanding or using the database correctly. Unit 1 could have say AO1, AO2, AO3, AO4, AO5 and Unit 3 could have A01, AO2, AO3, AO4 - will that database design still work for this? – Robin L Feb 12 '13 at 20:25
  • The official documents which may help: http://www.ocr.org.uk/Images/81821-unit-01-ict-skills-for-business-2010-assessment-coversheet.pdf http://www.ocr.org.uk/images/81819-unit-03-digital-imaging-plan-and-produce-computer-graphics-2010-assessment-coversheet.pdf http://www.ocr.org.uk/images/81823-unit-07-databases-design-and-use-2010-assessment-coversheet.pdf – Robin L Feb 12 '13 at 20:27
  • Yes, this is why I have the two tables `tlkpAssessment` and `tblUnitAssessment`. The first table describes the assessment objects and the second assigns (or links) them to units. You can assign the same assessment (`tlkpAssessment`) to several units (`tblUnit`) through the junction table `tblUnitAssessment`. This is an m to n relation as well. – Olivier Jacot-Descombes Feb 13 '13 at 13:10