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.