Help with normalization

[willlobb]

Hi All,

It's been 9 years since I last posted on here! and hence 9 years since my last big Access project...

I'm fairly confident with implementing the project in Access; creating queries, forms, etc. but before I even think about starting that I'm trying to decide the best way to normalize my data.

I am designing a database to track the English language achievements of pupils in 29 school in a country. Each school has students in years 7, 8, 9, 10, 11. There are approximately 20 numerical fields relating to each one of the tests that each pupil takes during an academic year. I need to design the database in such a way that when a student is in year 7, their academic progress for that year is show in a subdatasheet. When the student enters year 8 the following year, the for then shows year 7 in a collapsed subdatasheet with the new year 8 data below in a separate subdatasheet.

My problem is not the datasheets themselves, but rather how to normalize my data. Since the recorded data has the same field title for example "Year 7 Vocab Test Result" then "Year 8 Vocab Test Result" with only the year being different, should I create a separate table for the data in each year or would it be better to create one table with the field title "Vocab Test" without a year assigned to it?

I hope that makes sense and someone can please get me back on track. Any other suggestions would be greatly appreciated.

Thanks

 

[willlobb]

Great, thank you again for your replies - all sounds sensible.

Many thanks,
Will

 

[willlobb]

Hi again - just having a bit of an issue still...

Since I am creating a single table to store all of my tests, I am having trouble seeing how I will validate the data which will go into the "Test Score" field. Instead of having a table with, for example:

Year 9 Vocab Test Result
Year 9 Reading Age
Year 10 Vocab Test Result
Year 10 Reading Age
etc...

I have a table with the following:

TestRecordID (PK)
TestName (FK)
Yeargroup (FK)
Class ID (FK)

and another table with:

TestScore ID (PK)
TestRecord ID (FK)
Test Name (FK)
StudentID (FK)
YearGroup (FK)
Class ID (FK)
Test Score

But since each test will require a different type of data, i.e. Vocab Test will be a number but Reading age will be a range selected from a combobox, how can I set these different data types?

Many thanks
Will