Grade Table Design Help

dpbarry

Board Regular
Joined
Feb 10, 2003
Messages
197
Hi all..

I work in a school and my HOD wants to computerise our exam results but I am haing problems with the tables and would appreciate some guidance.

To describe our setup:

We have 920 odd students who study up to 15 subject and we wish to record the marks/grades that the students obtain for these subjects. Some subject are taught by more than one teacher as per the departmental setup so I am surmising that I will need tables for the following:

Students: StudenstID, Surname, Forename, Class Reg, Notes

Subjects: SubjectID,Subject, Notes

Grades: {studentID, SubjectID}, Grade, Notes

Teachers: TeacherID, Surname, Salutation

TeachSub: {TeacherID, SubjectID}

One of my problems is setting up and linking the Teachers and TeachSub tables(s) info to record the teacher details and the subject taught as a teacher can teach one or more subjects.

Are my table layouts correct and are there any missing?

Would anyone be able to assist and/or dummy up a quick setup in access to see the design.

Many thanks

Declan
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
Windows
Declan

As far as I can see you have the correct tables?

What exactly are you having problems with?

In the TeachSub table you would have a record for each combination of teacher/subject.

So if a teacher teaches 3 subjects, they will appear 3 times.

If a subject is taught by 4 different teachers it will appear 4 times.
 

dpbarry

Board Regular
Joined
Feb 10, 2003
Messages
197
Hi Norie..

Thanks for that. Both myself and my HOD sat down today without any interruptions and looked at what data we actually needed to see in our database.

We looked at the information that needs to be exported out of our schools main SIMS database system and we narrowed it down more or less on screen to a particular set of data. Some of the info I previously posted may be out of date based on what we achieved.

Our SIMS system is able to export out a list of each pupils subjects that she is taking along with the teacher that takes her for the subject so somwhere we need to incorporate a grade into a table based on this info.

As I'm at home at the moment, most is from memory but our main problem seemed to be in relation to what way to set up tables for the subject,grade and teacher.

I hope to produce a screen dump of the info from our SIMS system to give you an indication of the data that is being exported so that we can look at the necessary table layout as we think our initial table layout needs revised.

Speak soon.

Declan
 

dpbarry

Board Regular
Joined
Feb 10, 2003
Messages
197
Hope this looks okay.

As you can see, I have 8 fields. What is the best way of setting up tables for this data.

UPN is a unique number that identifies the student. Class identifies the subject and the class it is associated with. Teacher is naturally the teacher teaching the subject. Subject is just the Subject name to assist in understanding the field Class.

Grade is where I hope to add the grade where necessary.

What I would like to be able to do is be able to filter by teacher and/or Class, that will show all the pupils in this particular class so that the teacher can enter a grade.

Hope this makes sense.

p.s Sorry, I'm not sure why the '&nbsp' is showing up in the excel screen dump.

Declan
dummy data.xls
ABCDEFGH
1LegalSurnameLegalForenameUPNRegClassSubjectTeacherGrade
2DoeJaneL04400860009010D10DE2/ArArtMrsTMageean-
3DoeJaneL04400860009010D10DE2/MuMusicMrsGCochrane-
4DoeJaneL04400860009010D10DE2/TlTechnologyMrsKAndersson-
5DoeJaneL04400860009010D10D/FrFrenchMrsAKerr-
6DoeJaneL04400860009010D10D/GgGeographyMrsMMcCafferty-
7DoeJaneL04400860009010D10D/HiHistoryMrGToner-
8DoeJaneL04400860009010D10D/MaMathsMrsPMurray-
9DoeJaneL04400860009010D10D/ReReligionMrDBreen-
10DoeJaneL04400860009010D10D/FpFormClassMrsAMcGinn-
11DoeJaneL04400860009010D10DC/Ir1IrishDrBO'hEireamhoin-
12DoeJaneL04400860009010D10CD/Sp1SpanishMrJLeonard-
13DoeJaneL04400860009010D10DEc/TlTechnologyMrsKAndersson-
14DoeJaneL04400860009010D10DEc/SeEPRMrsCHanna-
15DoeJaneL04400860009010D10DE/Sc1ScienceDrAO'Neill-
16SmythMaryD04400860009310A10A/EnEnglishMissHWright-
17SmythMaryD04400860009310A10A/FrFrenchMrsCMartin-
18SmythMaryD04400860009310A10A/GgGeographyDrBHenry-
19SmythMaryD04400860009310A10A/ReReligionMrNWilson-
20SmythMaryD04400860009310A10Aa/HeH.EconMrsFNoonan-
21SmythMaryD04400860009310A10A/FpFormClassMrNWilson-
22SmythMaryD04400860009310A10AC1/ArArtMissJMcGrail-
23SmythMaryD04400860009310A10AC1/PePEMrsHMcCambridge-
24SmythMaryD04400860009310A10AC1/TlTechnologyMrsKAndersson-
25SmythMaryD04400860009310A10A/ScScienceMrsECollins-
26SmythMaryD04400860009310A10EA/Ir1IrishMrsCHanna-
27SmythMaryD04400860009310A10AE/Sp1SpanishMrsKCraig-
28SmythMaryD04400860009310A10AC1/MuMusicMrsGCochrane-
Sheet1
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top