Prev: Lists of Commands - stevie is pimping again
Next: "collecting data via email" when recipient uses Hotmail or Yah
From: John W. Vinson on 6 Jan 2010 19:52
On Wed, 6 Jan 2010 19:44:13 -0500, "Gina Whipp" <NotInterested(a)InViruses.com>
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]
From: forest8 on 6 Jan 2010 23:58
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" <NotInterested(a)InViruses.com>
> 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]
> .
>
From: forest8 on 7 Jan 2010 00:26
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" <NotInterested(a)InViruses.com>
> > 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]
> > .
> >
From: John W. Vinson on 7 Jan 2010 01:20
On Wed, 6 Jan 2010 21:26:03 -0800, forest8 <forest8(a)discussions.microsoft.com>
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]
From: Gina Whipp on 7 Jan 2010 01:27
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" <forest8(a)discussions.microsoft.com> wrote in message
news:B9A160C5-3A01-467B-ADC8-9C4EC626BA33(a)microsoft.com...
> 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"
>> > <NotInterested(a)InViruses.com>
>> > 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]
>> > .
>> >


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Lists of Commands - stevie is pimping again
Next: "collecting data via email" when recipient uses Hotmail or Yah