Relationships and Keys

On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp"
wrote:

John,

I have been trying to get them... Even helped out with one of my posts but
to no avail.

Sigh. Sounds like forest8 just wants to complain, not to solve his or her
problem. Not my loss!
--

John W. Vinson [MVP]

Hi

I do appreciate all the help and advice I am getting from both you and Gina.
I am not complaining just seeking answers and hopefully understanding. This
is the first Access database I am creating with little experience in Access.

In my tblStudent (this is the table which includes all the student info),
there is ID (autonumber and Primary key), StudentID, Firstname, LastName,
Birthdate, EntryYear, Major.

In the tblGrades, the ID, StudentID, Course, Grade, Teacher, Mark1, Mark2,
Mark3, ActiveStudent. This time, StudentID is the PK, and I'm trying to link
this to the tblStudnt.

In the tblTeacher, the ID, StudentID, Teacher, Course, AverageClassMark.
(This time, TeacherID is the PK) and I'm tying to link this to the tblGrades
table.

In the tblCourse, the ID, StudentID, ClassEvaluation, NoStudentPass. (Here
I'm trying to link the student back to the tblStudent). Am I just doing this
wrong?

In the tblSurvey, the ID, StudentID, Teacger ID, ExpectationsofCourse,
Major, (there are variables which deal with different subjects such as rate
the instructor, materials used, teaching, tutuorials, lectures, etc.). Here
I'm not sure what the PK should be.

The thing is, if I can create relationships between these tables, I can use
this logic to fix all the relationships.

I am also trying to apply what advice you both have given me as I tried to
figure out what to do. Not ignoring any of the advice.

Thank you again!


"John W. Vinson" wrote:

On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp"
wrote:

John,

I have been trying to get them... Even helped out with one of my posts but
to no avail.

Sigh. Sounds like forest8 just wants to complain, not to solve his or her
problem. Not my loss!
--

John W. Vinson [MVP]
.

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.

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). 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).

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.

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.

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.

Thank you in advance.



"forest8" wrote:

Hi

I do appreciate all the help and advice I am getting from both you and Gina.
I am not complaining just seeking answers and hopefully understanding. This
is the first Access database I am creating with little experience in Access.

In my tblStudent (this is the table which includes all the student info),
there is ID (autonumber and Primary key), StudentID, Firstname, LastName,
Birthdate, EntryYear, Major.

In the tblGrades, the ID, StudentID, Course, Grade, Teacher, Mark1, Mark2,
Mark3, ActiveStudent. This time, StudentID is the PK, and I'm trying to link
this to the tblStudnt.

In the tblTeacher, the ID, StudentID, Teacher, Course, AverageClassMark.
(This time, TeacherID is the PK) and I'm tying to link this to the tblGrades
table.

In the tblCourse, the ID, StudentID, ClassEvaluation, NoStudentPass. (Here
I'm trying to link the student back to the tblStudent). Am I just doing this
wrong?

In the tblSurvey, the ID, StudentID, Teacger ID, ExpectationsofCourse,
Major, (there are variables which deal with different subjects such as rate
the instructor, materials used, teaching, tutuorials, lectures, etc.). Here
I'm not sure what the PK should be.

The thing is, if I can create relationships between these tables, I can use
this logic to fix all the relationships.

I am also trying to apply what advice you both have given me as I tried to
figure out what to do. Not ignoring any of the advice.

Thank you again!


"John W. Vinson" wrote:

On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp"
wrote:

John,

I have been trying to get them... Even helped out with one of my posts but
to no avail.

Sigh. Sounds like forest8 just wants to complain, not to solve his or her
problem. Not my loss!
--

John W. Vinson [MVP]
.

On Wed, 6 Jan 2010 21:26:03 -0800, forest8
wrote:

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.
--

John W. Vinson [MVP]

forest8,

It's 1:30 AM where I am and I'm about shot so *later* today you will get a
more comprehensive answer tomorrow (provided no one else picks up my slack)
with a correction in your table layout PK's and FK's.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"forest8" wrote in message
...
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.

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). 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).

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.

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.

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.

Thank you in advance.



"forest8" wrote:

Hi

I do appreciate all the help and advice I am getting from both you and
Gina.
I am not complaining just seeking answers and hopefully understanding.
This
is the first Access database I am creating with little experience in
Access.

In my tblStudent (this is the table which includes all the student info),
there is ID (autonumber and Primary key), StudentID, Firstname, LastName,
Birthdate, EntryYear, Major.

In the tblGrades, the ID, StudentID, Course, Grade, Teacher, Mark1,
Mark2,
Mark3, ActiveStudent. This time, StudentID is the PK, and I'm trying to
link
this to the tblStudnt.

In the tblTeacher, the ID, StudentID, Teacher, Course, AverageClassMark.
(This time, TeacherID is the PK) and I'm tying to link this to the
tblGrades
table.

In the tblCourse, the ID, StudentID, ClassEvaluation, NoStudentPass.
(Here
I'm trying to link the student back to the tblStudent). Am I just doing
this
wrong?

In the tblSurvey, the ID, StudentID, Teacger ID, ExpectationsofCourse,
Major, (there are variables which deal with different subjects such as
rate
the instructor, materials used, teaching, tutuorials, lectures, etc.).
Here
I'm not sure what the PK should be.

The thing is, if I can create relationships between these tables, I can
use
this logic to fix all the relationships.

I am also trying to apply what advice you both have given me as I tried
to
figure out what to do. Not ignoring any of the advice.

Thank you again!


"John W. Vinson" wrote:

On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp"

wrote:

John,

I have been trying to get them... Even helped out with one of my
posts but
to no avail.

Sigh. Sounds like forest8 just wants to complain, not to solve his or
her
problem. Not my loss!
--

John W. Vinson [MVP]
.

forest8,

I'mmmm back! I see John has given you some real good advice which includes
reviewing Crystal's video. I hope you have done so. I am REdoing your
tables and outlining PK's and FK's and proper relationships. and some notes.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"forest8" wrote in message
...
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.

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). 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).

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.

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.

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.

Thank you in advance.



"forest8" wrote:

Hi

I do appreciate all the help and advice I am getting from both you and
Gina.
I am not complaining just seeking answers and hopefully understanding.
This
is the first Access database I am creating with little experience in
Access.

In my tblStudent (this is the table which includes all the student info),
there is ID (autonumber and Primary key), StudentID, Firstname, LastName,
Birthdate, EntryYear, Major.

In the tblGrades, the ID, StudentID, Course, Grade, Teacher, Mark1,
Mark2,
Mark3, ActiveStudent. This time, StudentID is the PK, and I'm trying to
link
this to the tblStudnt.

In the tblTeacher, the ID, StudentID, Teacher, Course, AverageClassMark.
(This time, TeacherID is the PK) and I'm tying to link this to the
tblGrades
table.

In the tblCourse, the ID, StudentID, ClassEvaluation, NoStudentPass.
(Here
I'm trying to link the student back to the tblStudent). Am I just doing
this
wrong?

In the tblSurvey, the ID, StudentID, Teacger ID, ExpectationsofCourse,
Major, (there are variables which deal with different subjects such as
rate
the instructor, materials used, teaching, tutuorials, lectures, etc.).
Here
I'm not sure what the PK should be.

The thing is, if I can create relationships between these tables, I can
use
this logic to fix all the relationships.

I am also trying to apply what advice you both have given me as I tried
to
figure out what to do. Not ignoring any of the advice.

Thank you again!


"John W. Vinson" wrote:

On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp"

wrote:

John,

I have been trying to get them... Even helped out with one of my
posts but
to no avail.

Sigh. Sounds like forest8 just wants to complain, not to solve his or
her
problem. Not my loss!
--

John W. Vinson [MVP]
.

forest8,

Below I have redone your tables and corrected your relationships. You seem
to under the impression that since your database is about Students every
table should have StudentID as a PK when that is not how relational
databases work, nor is that the case. Now if the below makes little or no
sense to you then I have posted some links after the tables which AFTER
viewing Cyrstal's video you need to read, save and read again. Tables much
like foundations to houses, if it's not right and you build the house...
it's coming down and so will your database!

Keep in my the below may need some tweaking based your your exact needs but
hopefully this will get you pointed in the correct direction.

tblStudents
sStudentID (PK-Autonumber)
sInternalStudentID (Text - This is the one used by the School)
sFirstName
sLastName
sBirthDate
sEntryYear
sMajor
sActive
etc...

tblStudentCourses
scStudentCourseID (PK-Autonumber)
scStudentID (FK-related to tblStudents:sStudentID)
scCourseID (FK-related to tblCourses:cCourseID)
scStudentGradeID (FK-related to tblStudentGrades:sgStudentGradeID)
etc...

tblStudentGrades
sgStudentGradeID (PK-Autonumber)
sgGrade
sgMark
etc...

tblTeacher
tTeacherID (PK-Autonumber)
tFirstName
tLastName
tCredentials
etc...

tblCourses
cCourseID (PK-Autonumber)
cTeacherID (FK-related to tblTeachers:tTeacherID)
cCourseName
cAverageClassMark
etc...

tblSurvey
sSurveyID (PK-Autonumber)
sSurveyName
sTeacherID (FK-related to tblTeachers:tTeacherID)
sCourseExpectations
etc..

tblSurveyDetails
sdSurveryDetailsID (PK-Autonumber)
sdSurveyID (FK-related to tblSurvey:sSurveyID)
sdMajor
etc...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html

You also need to be a wee bit more careful when selected field names and
here's why...
http://allenbrowne.com/Ap****ueBadWord.html

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"forest8" wrote in message
...
Hi

I do appreciate all the help and advice I am getting from both you and
Gina.
I am not complaining just seeking answers and hopefully understanding.
This
is the first Access database I am creating with little experience in
Access.

In my tblStudent (this is the table which includes all the student info),
there is ID (autonumber and Primary key), StudentID, Firstname, LastName,
Birthdate, EntryYear, Major.

In the tblGrades, the ID, StudentID, Course, Grade, Teacher, Mark1,
Mark2,
Mark3, ActiveStudent. This time, StudentID is the PK, and I'm trying to
link
this to the tblStudnt.

In the tblTeacher, the ID, StudentID, Teacher, Course, AverageClassMark.
(This time, TeacherID is the PK) and I'm tying to link this to the
tblGrades
table.

In the tblCourse, the ID, StudentID, ClassEvaluation, NoStudentPass. (Here
I'm trying to link the student back to the tblStudent). Am I just doing
this
wrong?

In the tblSurvey, the ID, StudentID, Teacger ID, ExpectationsofCourse,
Major, (there are variables which deal with different subjects such as
rate
the instructor, materials used, teaching, tutuorials, lectures, etc.).
Here
I'm not sure what the PK should be.

The thing is, if I can create relationships between these tables, I can
use
this logic to fix all the relationships.

I am also trying to apply what advice you both have given me as I tried to
figure out what to do. Not ignoring any of the advice.

Thank you again!


"John W. Vinson" wrote:

On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp"

wrote:

John,

I have been trying to get them... Even helped out with one of my posts
but
to no avail.

Sigh. Sounds like forest8 just wants to complain, not to solve his or her
problem. Not my loss!
--

John W. Vinson [MVP]
.

Hi John

Thank you for the advice and the link to Crystal's tutorials. I am in the
process of reviewing both.



"John W. Vinson" wrote:

On Wed, 6 Jan 2010 21:26:03 -0800, forest8
wrote:

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.
--

John W. Vinson [MVP]
.

Hi John

Thank you for your help. Wanted to tell you that your assistance, I was able
to create relationships.

Thanks again.

"forest8" wrote:

Hi John

Thank you for the advice and the link to Crystal's tutorials. I am in the
process of reviewing both.



"John W. Vinson" wrote:

On Wed, 6 Jan 2010 21:26:03 -0800, forest8
wrote:

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.
--

John W. Vinson [MVP]
.