An addendum to my last post. The ID in each table is an autonumber. All other
variables (if they're not dates which are in the data format or comments
which are I have designated as memo formats) are text.
The StudientID (text format) in the tblStudent is the primary key. The
StudentID is actually the primary key in all the tables except tblStudent
where the ID (which is an autonumber) is the PK.
But you're joining - *NOT* on ID, which is the primary key and has a unique
index - but on StudentID, which is *NOT* the primary key and does *NOT* have a
unique index!
You need to either join on the PRIMARY KEY, or create a unique Index on
StudentID (which would make either ID or StudentID redundant and unnecessary).
Calling a field "StudentID" does not make it unique, and does not make it a
primary key.
What I want is to connect all the tables to the tblStudent. All students
only have a single Student number, but they may enrol in several courses (not
all the same).
That means that StudentID must *not* be the primary key of the Courses table.
That would mean that each student may take one, and only one, course; since
the primary key is by definition uinque.
The teachers may teach the same course and not all all the
students so I wanted to link the teacher to the students in their classes.
Not all the students will be evaluating all the teachers (jut those who are
teaching them).
That is not necessary. Each table must have *ITS OWN* primary key, independent
of any other table's primary key! The table of Courses must have a CourseID
that uniquely identifies that course; the table of Faculty must have a
FacultyID or TeacherID which uniquely identifies that teacher; you'll have a
many to many relationship between courses and students, so you'll need an
Enrollment table with a StudentID (which student is enrolled) and a CourseID
(which course is she enrolled in), *NEITHER* of which is the primary key of
the table!
Students can repeat courses to improve their marks and as such can have more
than 1 teacher for a course which would be taken at different times and be
evaluated each time.
Exactly. That's what a normalized table design - *with appropriate primary
keys, which you do not currently have* - will let you do. You'll have (almost
exclusively) one to many relationships between your tables.
Students when they join the school are asked to complete a survey (their
results are linked to their StudentID and the first table). WHen they leave
the school, they're asked a post survey. Throughout their time at the
school, they may be asked a survey during the stay.
So you need a table of Surveys, related one to many to a table of
SurveyResults, which is related many to one to a table of Students.
As I mentioned in my last post, if I can establish relationships between
these tables I believe I can fix all the relationships in my table.
You bet. Start by understanding what a primary key *is* - a unique identifier
for the records *IN THAT TABLE* - and making your relationships from that
field to (matching) foreign keys in the related tables.
If you have not already done so, study Crystal's tutorial in:
A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html
or
A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal
paying special attention to the chapter on Normalization.
