Database for school assessment results

[Timetable]

Hello,I've been searching for a while on the internet and these forums, but I can't quite wrap my head around how to achieve what I want to.Currently I have a number of spreadsheets tracking data for a secondary school. There are 5 year groups. Within each year group are two or more ability groups. Each ability group does a number of assessments through the year. Each assessment's data is inputted question by question under a new worksheet (Test 1, Test 2, etc). I have attached a sample attachment to illustrate this setup a little better.These spreadsheets have a lot of duplicate data, and I feel a move to Access would make the data management more straightforward. My issue is I can't quite understand how to set the database up.My initial thoughts were as so:One teacher can have many classesOne class can have many studentsOne student can have many testsOne test can have many students (that attempt it)One test can have many questionsOne question can have many answers (from different students)I'm at a loss as to how to set this up effectively to encompass all year groups' data. If anyone could please help me work out how to do this, I'd be extremely grateful.Thank you.

[Timetable]

Apologies for the formatting. For some reason it removed all paragraph formatting and I cannot save my edit to the post.

[ranman256]

This may get you started structure wise: tables and fields

tStudents
--------
StudentID auto
First
last
addr
city
st
zip


tTeachers
--------
TeachID auto
First
last
addr
city
st
zip



tSchoolYr (Each school year/semester)
--------
YearID auto
School
year


tClasses (classes available to take) *not sure if it applies to your structure
--------
ClassID auto
YearID
ClassName
TeachID


tAbGroups (ability groups available)
--------
GroupID auto
GroupName



tAbGroupsYr (ability groups for the year)
--------
AbGrpYrID auto
GroupID
YearID


tGroupRoster (students in the group that year)
--------
RosterID auto
AbGrpYrID
StudentID



tTests (tests that were given for a group)
----------
TestID auto
AbGrpYrID
Date



tStudentTests
----------
StudentTestID auto
TestID
RosterID
Grade

[Timetable]

That seems great, thank you. I shall get back to you once I have the relationships sorted. A major sticking point, however, is the StudentTests table - this is what's causing me most trouble in creating my own database. We currently input data question by question, and each question has an associated topic. This allows us to run checks on what people are consistently getting wrong, so we can focus teaching on that topic area. In the past I've tried having "Question 1", "Question 2", "Question 3", etc. fields where the user inputs the score for each corresponding question. The only issue when I do that is I can't see what topic each question was, nor can I see how many marks were available (which is easy to do at a glance using my current spreadsheet system). Have you any ideas how I can overcome this? I really want to move our data to Access, but without being able to see what topic or how many marks are available for each question I cannot warrant the move to my colleagues. Thanks again!

[Timetable]

Sorry for the double post, but again the formatting has gone awry and it won't let me save changes to my edit. Just as general forum advice, how do I avoid this problem?

[orange]

To better understand database design concepts - tables, attributes and relationships - work through this tutorial from RogersAccessLibrary. You will learn concepts by experiencing the process of working from a problem definition to an entity relationship diagram which is the blueprint of your database.
There are other database design tutorials with solutions. Work through some of these until you understand the concepts. It will save you hours of trial and error and will also show where design fits in the process of database development.

Good luck.

[ranman256]

sorry, didnt know you wanted the actual test questions stored....so something like:

tTest
[testID]
[DateOfTest]

tTestQs
[TestQID]
[TestID]
[Q]
[A]