Help needed creating a database design to track pupil milestone progress

[hammermad]

I seek help please. I haven't used Access in anger for around 20 years. I am a teacher in London and want to put a database together to track pupil progress. At the moment the process is paper / pen / spreadsheet and incredibly time intensive - it is also impossible to recall and report on any data easily to target progress.



Scenario:

Pupil A is learning a subject, and that subject has 30 milestones for one academic year. The school tracks 3 subjects, so over 6 years one pupil would have 540 milestones (3 subjects x 6 years x 30 milestones). When you expand that to 30 in a class, and 4 classes in each year group it means the school is tracking 64,800 milestones across the whole school. Other subjects are tracked but not tracked in the same way, there are 14 subjects in total.

End goal:

The teacher opens a form that shows the pupils in their class, they select the pupil and they see the number of milestones that pupil has met for each of the 3 subjects. The teacher needs to be able to open a subject and enter that a milestone has been achieved, ideally with the date. I feel comfortable enough with queries to put together reports and forms to show class focus (ie as a class, what milestones are they all missing for each subject).

I have started setting up tables for students, milestones and forming relationships but I'm hoping you can collectively point me in the right direction.

I really don't want this to become a political post, so in summary; the data is for internal use only recorded to track progress for any potential inspection. The school is unable to fund any solution already built. In addition, the school does not have the IT infrastructure to support anything more complex than an Access database (The use of Excel formulas is seen as wizardry and cloud computing is beyond comprehension).

EDIT: School is using Access 2016. Some computers are still Windows 7, most are Windows 10.

[CJ_London]

so to clarify the background:

Every teacher has their own computer?
Every teacher's computer has Access installed?
Every version of Access is the same?
And those computers are all connected on a LAN to a central server?

Suggest post a screenshot of what you currently have of your tables and relationships

[hammermad]

Yes to all the questions below. Screenshot added of the relationships, the M1Completed are Yes/No.

so to clarify the background:

Every teacher has their own computer? Yes
Every teacher's computer has Access installed?Yes
Every version of Access is the same? Yes
And those computers are all connected on a LAN to a central server? Yes

Suggest post a screenshot of what you currently have of your tables and relationships

[Micron]

Put the brakes on - not right. First you have repeating fields (multiple fields for the same type of information). Then you have tables for what arguably is similar/same information. Likely you should have a table for subjects and a table for Milestones. You'd also need junction table(s) but not sure what. Perhaps that would be dictated by the pupils vs subjects conditions (e.g. can 1 student take many subjects at the same time vs 1 at a time). Same could be said for teachers vs subjects.

Perhaps if you search and review many of the student/subject models you can find it might give you some more insight.

[ArviLaanemets]

Let''s assume you have a student. Is 6 years the length of full study for student? And when yes, can there be students having less (or more) years, and how cope with them?
Did I understand correctly, through an single academic year, a student studies 3 different subjects (not less, and not more)?
What about the whole 6-year period? Does this student study same 3 subjects in every of those 6 years, or different 3 subjects in every academic year (18 different subjects through all 6 years), or something between?

[NoellaG]

Just some more topics to think about before starting:
- How many people will simultaneously be logged in to the database?
- +/- How many updates/reads?
- +/- How many rows will the database contain after 3 years work?
- Do you want to keep historical data or not?
- Do you want to use the system online (intranet site?)
- What kind of security do you want to apply? Can students use the database and do they have the same rights as the teachers?

[dblife]

In terms of table structure, I would go with something like that attached. I would keep the working form as simple as possible, also as shown.
Once the tables were populated with the necessary information, students, milestones etc, you could choose the student and subject from the combo box fields to filter the subform.
If you needed to add to the students record, use the button below the subform to add to 'tMilestoneRecord' using a simple form to add the record.
the important thing, the tables and data is normalised and data integrity is enforced in the relationship structure.
The 'class' data is not recorded here as it is student centred and the idea of 'class' is not stipulated... their form class or the teachers class? Is it even required?
There will be a lot of records and none of it should be able to go astray.