Grade Table Design Help

dpbarry

Board Regular
Joined
Feb 10, 2003
Messages
214
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top