Restructuring Tables

[Guest]

To Whom It May Concern

I need some advice or direction. I currently have a database that has one main table that contains test scores for various students. In addition, I have another table that contains the students and their assigned teacher

The database was not originally designed for the possibility of additional teachers. Since, this has occurred, I must now change the design to facilitate multiple teachers for each student. My goal is to have a record of each student’s test with the teacher that administered the test at that time

Please tell me if the following it the optimal way to restructure the data

Test Score Table
Test ID: P
Student Nam
Test Dat
Test Score
Test Comment

Students Table
Student ID
Student Nam
Teacher Nam
Active Status: Yes/N

Test History Table
Test History ID: P
Test I
Test Dat
Student Nam

Teachers Table
Teacher ID: P
Teacher Nam

In addition, the form that popluates the main table must retreive the student's current teacher. Any and all advice, help, or comments will be greatly appreciated

Sincerely
Glenn E. Whit

 

[PC Datasheet]

Glenn,

I recommend the following:

Students Table:
Student IDK
Student Name
Active Status: Yes/No

Teachers Table:
Teacher ID: PK
Teacher Name

Test Subject Table:
TestSubjectID: PK
TestSubject

Test Score Table:
TestScoreID: PK
TestSubjectID
StudentID
TeacherID
Test Date
Test Score
Test Comments


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

To Whom It May Concern:

I need some advice or direction. I currently have a database that has one

main table that contains test scores for various students. In addition, I have
another table that contains the students and their assigned teacher.

The database was not originally designed for the possibility of additional

teachers. Since, this has occurred, I must now change the design to facilitate
multiple teachers for each student. My goal is to have a record of each student
's test with the teacher that administered the test at that time.

Please tell me if the following it the optimal way to restructure the data:

Test Score Table:
Test ID: PK
Student Name
Test Date
Test Scores
Test Comments

Students Table:
Student IDK
Student Name
Teacher Name
Active Status: Yes/No

Test History Table:
Test History ID: PK
Test ID
Test Date
Student Name

Teachers Table:
Teacher ID: PK
Teacher Name

In addition, the form that popluates the main table must retreive the

student's current teacher. Any and all advice, help, or comments will be
greatly appreciated.

 

[Andrew Smith]

Each table should be about only one entity - so the student table is about
the STUDENT, and does NOT contain anything about the TEACHER. The TEST table
is about the TEST, and knows nothing about the STUDENTs etc.

Tables are related by key fields, so the StudentName, for example, is stored
ONLY in the Student table. Any related tables store on the StudentID.

Assuming that each test generates only ONE score for each student, I would
have thought something along the lines of:

tblStudent:
StudentID (PK)
StudentFirstName
StudentLastName
Active (Yes/No)

tblTeacher
TeacherID (PK)
TeacherFirstName
TeacherLastName

tblStudentTeacher
StudentTeacherID (PK)
StudentID (FK)
TeacherID (FK)
DateAssigned
(NB You could use StudentID and TeacherID as the PK, but only if a
student can never be assigned the same teacher more than once, which I doubt
you can guarantee. To find the current teacher, just create a query that
finds the teacher with the highest value of DateAssigned).

tblTest
TestID (PK)
TestDate
TestSubject

tblTestScores
TestID (FK, part of PK)
StudentID (FK, part of PK)
TestScore
TestComments

To Whom It May Concern:

I need some advice or direction. I currently have a database that has one

main table that contains test scores for various students. In addition, I
have another table that contains the students and their assigned teacher.

The database was not originally designed for the possibility of additional

teachers. Since, this has occurred, I must now change the design to
facilitate multiple teachers for each student. My goal is to have a record
of each student's test with the teacher that administered the test at that
time.

Please tell me if the following it the optimal way to restructure the data:

Test Score Table:
Test ID: PK
Student Name
Test Date
Test Scores
Test Comments

Students Table:
Student IDK
Student Name
Teacher Name
Active Status: Yes/No

Test History Table:
Test History ID: PK
Test ID
Test Date
Student Name

Teachers Table:
Teacher ID: PK
Teacher Name

In addition, the form that popluates the main table must retreive the

student's current teacher. Any and all advice, help, or comments will be
greatly appreciated.