School Database Advice (1 Viewer)

[Nytmode]

Hi, I’m trying to make a small database for my school for making my students performance analysis easier. I’m doing all this manually in excel since the past 3 years but its getting more and more complicated and time consuming very year so I have thought of shifting to a database.

I have a small background in software engineering but nothing at professional level. Just wanted some advice on the tables and relationships that I would need for this database to work. So my school has grades, then sections for these classes example Grade 7A, Grade 7B, and normal subjects like Science, Maths, English etc. I need to store marks for students for every subject for each year separated term-wise. 3 terms every year.

So this is what I have come up with but not sure if its correct. 3 tables for now. Student, Marks, Class. This is what the relationship looks like (attached image) I should be able to make reports on every student, every class, grade-wise, gender-wise, nationality-wise, term-wise etc etc. Then compare the same student with his last years marks and compare the different grades and sections etc etc. So how to do it? I’m trying it in Ms Access as making reports with the wizard is simpler.

P.S I will be importing all the data through excel sheets into this database.

 

[Uncle Gizmo]

I have covered moving data from Excel to access in my blog here:-

https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

One of the examples actually uses "students" to demonstrate the issues people face in moving from Excel to MS Access...

Sent from my SM-G925F using Tapatalk

 

[isladogs]

Hi

Adding to Tony's reply, I should be able to give you some detailed advice as this is my area of work.

However I'm going to be out enjoying the UK heatwave all day.
For now I'll just make the following points regarding imported data:

1. Use the same notation for your PK & FK fields in each table e.g. SID, CID, MID, TID (Teacher) as it will be easier to understand later

2. Student table - use separate fields for LastName & FirstName, add tutor group etc.
Use the school unique ID e.g. UPN or admission number as the ID field

3. Class table - add TeacherID

4. Marks table - replace Eng/Math/Science with Subject & Mark fields
That will reduce the number of fields & allow for additional subjects later
You can use crosstab queries to get a spreadsheet like output later

5. Add tables Teachers (TeacherID, LastName, FirstName) & Subjects (SubjectID, Subject)

On my website, you can find a DEMO version of my School Data Analyser database complete with example data for a fictitious school.
http://www.mendipdatasystems.co.uk/school-data-analyser

Its not the latest version but should be useful to you for ideas.

This does lots of things including reports & exams analysis
Its an ACCDE file so you can't see the code
You can view the table designs which may give you some useful ideas

If you do look at it, read the accompanying info on the website
Use 111 for the UserName & Password on the login screen

EDIT
Just realised you have cross posted this at:
http://www.utteraccess.com/forum/index.php?showtopic=2044493
and at
http://www.accessforums.net/showthread.php?t=66919

@nytmode
Whilst its fine to use more than one forum, you should always state you have done so & provide the links (as above).
Otherwise, people will waste time making the same points already covered elsewhere