Hello, and thanks in advance for your time and help.
It has befallen to me to test struggling students in a high school with several different standardised tests over a period of three or four years. These results are used to track whether they are catching up or falling behind, and to gather evidence for exam boards when applying for 'special access' in exams (Eg. 25% extra time). Up until now the tests have been printed and put in a paper folder. When we want to evaluate a student, we have to put all these sheets of paper onto a tabletop in chronological order, and try to pick out meaning from it. I think a database would serve us better, and would like to be able to maintain a digital student profile with all their historical test data nicely collated and tabulated. The profile would be a screen with the student's information, and then all the standardised tests they have been given over the years grouped into sections. It would be printable.
The trouble is, I've never really used Access before. I don't know what it can and can't do, nor how it likes to do things.
I've been watching some videos, reading a lot, and playing a little, but I can't quite get my head around the table design. Here's the 4 tables I have so far:
tblStudents tblTestType tblMeasure tblResults studentID TestID MeasureID ResultID FirstName TestName TestID StudentID LastName MeasureName Date Of Test Date Of Birth MeasureUnit TestID Current Age [calc'd] Result Cohort
And here's how some of the data might look when input (tblStudents is obvious, so I have excluded it):
tblTestType
TestID TestName 1 WRAT4 2 Hedderly 3 DRA 4 Dyslexia Portfolio 5 Hodder 6 NGRT
tblMeasure
MeasureID TestID MeasureName MeasureUnit 1 1 Single Word Reading Std 2 1 Sentence Comprehension Std 3 1 Spelling Std 4 1 Maths Std 5 2 Free Writing Speed Words/Minute
tblResults
ResultID StudentID Date of Test TestID MeasureID Result 1 40 10/1/17 1 1 90 2 40 10/1/17 1 2 83 3 40 10/1/17 1 3 90 4 40 10/1/17 1 4 85 5 22 12/2/17 2 5 20
Now I need to explain why I have doubts about whether this is the correct table design for my purpose.
These doubts are based on the ignorance, confusion and misconceptions that I have about queries, forms and reports.
Before I started to normalise things, I expected to have a table for each test-type, and so the results for one test go in one 'record' (row). Eg.
tblResultsWRAT
WRATID StudentID Date Of Test Single Word Reading Sentence Comprehension Spelling Maths 1 40 10/1/17 90 83 90 85 2 22 12/2/17 95 100 97 105
tblResultsHedderly
HedderlyID StudentID Date Of Test Free Writing Speed 1 22 12/2/17 90
I know this approach is not normalised, but it seems more intuitive to me with regard to data entry and retrieval. I can kind-of imagine how to build this input form. Whereas I cannot imagine how I will build the input form from the normalised structure - it seems to me that to input the results from one test (Eg. the WRAT), I would have to do 4 completely separate entries choosing different parameters each time, rather than put all 4 results into one form and click 'done'.
To list the tests in the 'student profile' screen (a form or report?), the query for the WRAT section of the profile would simply be something like 'list all records from tblResultsWRAT with StudentID 40'.
Again, I cannot imagine the queries I would need to collate the data using the normalised tables.
I would be very grateful for any advice anyone can give me at this point to lessen my bewilderment, especially in regard to whether it would be OK to intentionally 'flatten' the table structure.