update query will not work 2

[Guest]

Sorry my first question the format changed and moved the table structure.
This database has a few tables some of which are:
tblCourseOccurence
tblDivision (this is the same as a class)
tblStudentCourses
tblCourseExams
tblExamResults
briefly I can have many Divisions on a course, many students to a division,
many exams to a division and inturn the student and many exam results to a
student.

What I am trying to do is when a student is enrolled to a division on a
course I want certain fields from the tblStudentCourses table to populate the
same fields in the tblExamResults table.
And then when we create an exam that is attached to a division I want
certain fields from the tblCourseExams table to populate the same fields in
the tblExamResults table. I have tried an update query but it just will not
play the game for me it’s probably something really stupid but I just can’t
see it.
The tables are structured as below (not all fields but the main ones):

tblDivision tblStudentCourses
tblCourseExams DivisionID AutoNO StudentCourseID AutoNO
CourseExamID AutoNO
CourseName DivisionID
DivisionID
DivisionName StudentPersonalNumber ExamName

tblExamResults
CourseExamID (populated as exams are created)
ExamName (populated as exams are created)
DivisionID (populated as student is added to
division)
StudentCourseID (populated as student is added to division)
StudentPersonalNumber (populated as student is added to division)

I have tried to do this with a query but how the relationships are I can’t.
any help would be appreciated
Rodney

 

[John Vinson]

What I am trying to do is when a student is enrolled to a division on a
course I want certain fields from the tblStudentCourses table to populate the
same fields in the tblExamResults table.

Generally this is A Bad Idea. You're apparently storing data
redundantly. If you already have these fields stored in
tblStudentCourses, then they should (generally) NOT be stored in
tblExamResults as well.

Can you not simply create a Query joining the two tables to pull the
Exam Results from tblExamResults, and these fields from
tblStudentCourses?

John W. Vinson[MVP]

 

[Guest]

John,
Thanks for your reply
I initially intended to have all the Exam results on the tblStudentCourses
table, the reson I am looking at a seperate table for the exam results is
that I work for Department of Defence and am building this database for
Officer training which is a lot different to general training environments.
From initial training students are added to the database and will remain
there until they complete their continuum which is 6 to 7 courses over 10-12
years.
If I had the exam results on the tblStudentCourses I would end up with
approx 11 to 16 recordsets per student per course as each course has about 10
to 15 eaxms each plus the course pass and about 1500 students a year.
I thought it may be a bit neater or tidier having the exam results seperate
from the student table and just requiring the following fields

StudentCourseID
CourseExamID
DivisionID
PassCode
Score

I have tried a query but how the relationships are setup will not let me
have a exam to a Division (class) I need to have an exam per student on
course.
John any ideas would be greatly appreciated
Rodney