Grades Database for School

[dpbarry]

Hi all..

Just had this one dumped on me by the Principal who asked if I could set up a database that would hold the names of all the pupils in the school and their respectives grades, marks and effort for each individual subject that they do.

Has anyone a sample of this type of database. I'm not hot on MSAccess but would like to learn and this looks like a challenge.

Setting the tables up seems to be the biggest headache and based on my limited knowledge, I stabbed at a guess that the tables I'm going to need are tblstudents, tblsubjects and tblgrades.

My understanding is the students can take one or more subjects and a subject can have one or more grades attached to it but am I right in the way I interpret it all?

Based on a form, I would like the Teacher to select the relevant subject they teach from a dropdown menu which would say, bring up the subject 'Art' which would show in a subform, all the students who do Art and beside their name, would show the fields for Grade, Mark & Effort in which the staff would enter the relevant information.

IS this possible?

Regards

Declan Barry (confused)

 

[Andrew Fergus]

Hi Declan,

It sounds like you are on the right track and yes the table set-up (or the database design which is the table set-up and defining the table relationships) is the one of the hardest parts of Access.

I believe the "grades" table will be the crucial table in your database given it will link both the students and subjects tables (and given it holds the most pertinent information too!). I envisage the grades table would include fields such as a student id (linked to tblStudents), a subject id (linked to tblSubjects), a year (very important if your database is to survive for more than one school year) and the grade, mark & effort scores.

I found a school grades database tutorial on the internet here that may be useful - or at the very least it may give you some ideas (I haven't read it in detail). I think it is quite a large webpage so it may take a while to open.

HTH, Andrew.

 

[dpbarry]

So far, I've got the database working in such a way that I can view by subject in the main form BUT, it only shows students in the subform who have had their Grade, Mark and effort inputted into the database.

What I would like it to do (initially) is populate the subform with the full list of students irrespect as to whether their grades etc are inputted so that the teacher can see who is in the class and then enter their grades etc.

Anyone know how to do that?

Eventually, once I get that sorted out, I will then start to filter by the students Year and Class reg ID so that rather than having all students list, the teacher can see the list of puils in his/her class based on the filtering of Student Year and Class Reg ID . Have to walk first!

Regards

Declan

 

[dpbarry]

As it is, I can only get a list of all the pupils if their grades are entered in but that unfortunately is my problem. The grades aren't there in the first place as nothing has been entered yet so the query won't show anything .. or a least I can't get it to show anything.


If I can just get the subform to show the list of pupils irrespect as to where grades are entered or not, I'd be happy.

I'm going to keep at it and not let it beat me. The answer is somewhere.

Regards

Declan

 

[dpbarry]

Just been looking at another access grades database program which is very close to what I want but has the same problem of not showingstudents who have no gardes entered. The only way I can see out of it is to put Mark, Grade and Effort values in against every pupil and subject but is there an easy way of inporting from Excel.

My problem is the link values between tblstudents, tblsubjects and tblgrades given that tblgrades is made up from the primary keys of tblstudent and tblsubject.

For instance, Student 'Bloggs' with studentID = 1 will have a Grade, Mark and Effort for each subject and if there are 12 subjects, studentID=1 will show up 12 times in tblGrades which is fine for the actual database itself but a nightmare for me to enter default data for 940 pupils who all do 12 subjects - all just to show them up in a subform list so that staff can enter their actual Grade, Mark and effort.

Boy.. Some head scratching tonight!!

Regards

Declan

 

[Norie]

How are you actually joining the tables in the query?

 

[Andrew Fergus]

**Apologies in advance for the long post**

... but is there an easy way of inporting from Excel.

Hi Declan,

Do you already have some of the data in Excel? If so, then you would be better off importing the data you have from Excel into the intermediary table. You could import the student id, the subject id and year into the intermediary table but leave the mark, effort and grade fields blank (to be filled in later by the teacher). Please note that the column headings in the spreadsheet should match the field names in the table.

Unfortunately this will need to be done every year and with 900+ students with 12 subjects each, this is no small task. This table is (quite correctly) the crux of the whole database and populating it with student / subject records each year is a necessary evil.

I may be getting ahead of myself here but there may be some features you can employ in your database setup and design to make some tasks more manageable. Some of these tasks may not come into use until some months down the track whilst others may be useful immediately (and others some suggestions may be completely useless!).

Some examples of such features include :

• make sure your indexes and relationships are correctly set-up (absolutely crucial for the success of the database);
  
  each teacher only views the student / subject records for their subjects (i.e. assign a teacher to each subject for each year and the view in the subform is dependant upon the teacher selected in the main form);
  
  using forms and queries, a user can view the pupils enrolled for a selected subject, or view all of the subjects for a selected pupil (again using forms and subforms);
  
  assuming there is some continuity in subjects you could use the student / subjects stored in one year to provide a "suggested" list of student / subjects for the following year (to make the initial set-up a little easier, although it may be completely off-track - plus you will want to exclude students in their final year);
  
  allow for students to "drop out" part way through the year and also allow students to change subjects part way through the year (i.e. setup the database to allow any such "events" that occur at the school);
  
  you could set-up the data entry forms in your database to look and feel like the manual records you are using to populate the database (to make data entry faster and more accurate), and
  
  you might also want a status field against each student to allow for early departures or to indicate which year they are in and so forth (ditto for the teachers).

Unfortunately this is no small task that you are undertaking (as you have already identified) and I recommend that you follow your nose, make use of the online help and if you find yourself stuck just post a new subject here!

HTH, Andrew.

 

[dpbarry]

When I add the 3 tables in the query they automatically show the relationship link.

Between tblstudents and tblgrades is 1 to many and between tblsubjects and tblgrades is 1 to many. The common table between tblsubjects and tblstudents is tblgrades

Declan