Normalizing help needed, confused

[ARDRimmer]

Hi all,



I need help with normalization. I have employee db with training course information. There are 4 different courses, and a bunch of tests in each course. Employee may try to pass each course several times.

Employee(EmployeeID, LastName, FirstName, gender, department, position, phone#, e-mail, picture, notes)
Course(CourseID, Year, CourseName, CourseBeginDate, CourseEndDate, #ofEployees, #ofEmployeesCompletedCourse, ChiefInstructor, Instructor1, Instructor2, Instructor3. notes)
CourseAlphaResults(EmployeeID, CourseID, Course#(2012-01A 2012-year, 01-course# that year, A-Course designator), CourseBeginDate, CourseEndDate, Certificate#(if completed), AcademicTestTry1, AcademicTestTry2, AcademicTestTry3, AcademicTestTry4, PracticalTestTry1, PracticalTestTry2, PracticalTestTry3, PracticalTestTry4)
CourseBravoResults(EmployeeID, CourseID, Course#(2012-01B), CourseBeginDate, CourseEndDate, Certificate#(if completed), AcademicTestTry1, AcademicTestTry2, AcademicTestTry3, AcademicTestTry4, PracticalTestTry1, PracticalTestTry2, PracticalTestTry3, PracticalTestTry4)

I cant seem to get rid of redundancies. Employees may try same test 4 times and majority of them pass only on third time. I could leave only 1 test and record only if employee passed it or failed, but I won't see who passed on first try and who only on fourth. I read all examples on normalization from links on this forum with no help. As I understand now is that even if all 4 test are the same, they are different based on order taken. (I'm confusing myself). Test are different in each course. Do I still need 4 instances of the same test?

----------------AcademicTest1 ------ AcademicTest2
Employee 1 ----- Failed ----------------- Passed
Employee 2 ----- Passed

[R_Badger]

I think you are really close, but missing the last bit of normalisation.

You are storing the same data effective with the AcademicTestTry4, and practical testtry

I would suggest another table

TryID (autonumber) EmployeeID, CourseID, Testtype (academic or practical), date (and time if retested the same day), result (pass/fail etc)

That way regard of how many attempts it takes a person to pass something on a course it should be stored and easy to see how many attempts they took.

It's probably clear as mud but I will try to explain better if that doesnt make any sense

[ARDRimmer]

Thanks,
I see, could date and time be substituted with number of try, like
TryID (autonumber) EmployeeID, CourseID, TestName (there are 10 different tests), NumberofTry (1, 2, 3 or 4), result (pass/fail etc)

One of the tests is taken in pairs, both employees either pass or fail together. What about if course has multiple instructors?