Prev: Lists of Commands - stevie is pimping again
Next: "collecting data via email" when recipient uses Hotmail or Yah
From: Gina Whipp on 7 Jan 2010 09:44
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" <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]
>> > .
>> >


From: Gina Whipp on 7 Jan 2010 10:14
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/accessjunkie/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/AppIssueBadWord.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" <forest8(a)discussions.microsoft.com> wrote in message
news:100D6101-5A29-4C24-8947-FECBAF10539E(a)microsoft.com...
> 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 14:13
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 <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: forest8 on 15 Jan 2010 16:56
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 <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]
> > .
> >
First  |  Prev  | 
Pages: 1 2 3 4
Prev: Lists of Commands - stevie is pimping again
Next: "collecting data via email" when recipient uses Hotmail or Yah