Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

linked records/relationships

Discussion in 'Information Technology' started by sedonovan, Jul 28, 2006.

  1. sedonovan

    sedonovan
    Expand Collapse
    Guest

    dear all,

    I have a problem. I'm trying to establish a database for teachers, classes
    and students (which i've done), so far in one table, and 3 separate tables
    (t,c&s)

    now, i want to be able to select a teacher, for it to show me all the
    classes they teach (part of the class records have who teaches it) and THEN,
    when i select the c, to display all pupils linked to that class - part of the
    students records has which classes thy are enrolled in.

    is this possible, and if so, HOW??

    your help is greatly appreciated!!

    :eek:)
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Letter: When terrorism is falsely linked by race or religion, we all become the victims Breaking News Apr 27, 2013
    SciTech Law of Entropy Linked to Evolution of Intelligence Breaking News Apr 23, 2013
    Canada GM corn linked to early death in new study Breaking News Sep 22, 2012
    India set to ban 100 Al Qaeda-linked groups Hard Talk May 17, 2010
    Sikh News Temple linked to terrorists still active (Moldova.org) Breaking News Feb 5, 2008

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Sedonovan,

    You will have to explain a bit more about "it to show me". But the
    basic concept here, I think, would be a query, based on the Classes
    table, with a Criteria in the Teacher field to select which teacher
    whose classes you want to see. If you are selecting the Teacher using a
    combobox on a form, then the Criteria in the query can reference the
    combobox, using syntax such as...
    [Forms]![NameOfForm]![NameOfCombobox]

    As for the other question, this is a bit trickier. In the sense that I
    will presume that any given Student can be enrolled in more than one
    Class. As such, it is not appropriate that the Students' Classes are
    entered in the Students table. In fact, if this is what you have done
    so far, it is difficult to imagine how this has been achieved. No, you
    need an additional table, let's call it Enrolments, where you record the
    Students' Classes. Assuming you have an ID field of some sort to
    uniquely identify each Student in the Students table, and each Class in
    the Classes table, then the Enrolments table will require both StudentID
    and ClassID fields in order to record all Classes for each Student. You
    will need to get this table design sorted out before you will be able to
    get a query to return a listing of all Students for a selected Class.

    You might find it useful to look at this article...
    http://accesstips.datamanagementsolutions.biz/many.htm

    --
    Steve Schapel, Microsoft Access MVP

    sedonovan wrote:
    > dear all,
    >
    > I have a problem. I'm trying to establish a database for teachers, classes
    > and students (which i've done), so far in one table, and 3 separate tables
    > (t,c&s)
    >
    > now, i want to be able to select a teacher, for it to show me all the
    > classes they teach (part of the class records have who teaches it) and THEN,
    > when i select the c, to display all pupils linked to that class - part of the
    > students records has which classes thy are enrolled in.
    >
    > is this possible, and if so, HOW??
    >
    > your help is greatly appreciated!!
    >
    > :eek:)
     
  4. JK

    JK
    Expand Collapse
    Guest

    Hi Seonvan,

    Create 2 more tables

    1. TeachersClasses that have
    Teacher_ID
    Class_ID (preferable with the fields names as in your table
    (no need for any more fields)
    Mark both of them as Primary keys (this will ensure that you will not
    have a duplications of teacher-class combination twice or more

    2 ClassesStudents
    Class_ID
    Student_ID
    (both as primary as above)

    3. create one-to many relationships:
    Teacher table and TeatchersClass on Teacher_ID
    Classes table and TeatchersClass on Class_ID
    ClassesTable and ClassesStudents table on Class_ID
    Student Table and ClassesStudents table on Student_ID

    To see all the classes for a teacher:
    create a teachersClasses (qTeachClass) query using TeachersClasses and
    the teacher tables
    (Teacher_ID has to come form the TeachersClass table)

    Same for Classes- Students combination (qClassStud)

    Create a Teacher form from the teacher table
    create A teacherClass subform from qTeachClass (as a SINGLE form)
    Create a ClassesStudents subform from qClassStud ( as a CONTINUED
    form) Link it to TeacherClasess Subform on Class_ID

    Link the TeacherClass Sub form to the teacher for on Teacher_ID. You will
    get a 3 levels form/Subform

    As you can see by using those two additional tables you can have any
    combination you want by creating different queries, eg,
    Teachers-Student (no classes), Student-Teachers etc.)

    I hope that I did not confuse you

    Regards
    JK



    "sedonovan" <sedonovan@discussions.microsoft.com> wrote in message
    news:1A8CD73D-37D7-4459-AFA7-372D273DD1EC@microsoft.com...
    > dear all,
    >
    > I have a problem. I'm trying to establish a database for teachers, classes
    > and students (which i've done), so far in one table, and 3 separate tables
    > (t,c&s)
    >
    > now, i want to be able to select a teacher, for it to show me all the
    > classes they teach (part of the class records have who teaches it) and
    > THEN,
    > when i select the c, to display all pupils linked to that class - part of
    > the
    > students records has which classes thy are enrolled in.
    >
    > is this possible, and if so, HOW??
    >
    > your help is greatly appreciated!!
    >
    > :eek:)
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    JK,

    JK wrote:
    > 1. TeachersClasses that have
    > Teacher_ID
    > Class_ID (preferable with the fields names as in your table
    > (no need for any more fields)
    > Mark both of them as Primary keys (this will ensure that you will not
    > have a duplications of teacher-class combination twice or more


    I'm pretty sure this is not correct. This would only apply if a Class
    can have more than one Teacher. Otherwise the Teacher goes in the
    Classes table, which I believe is what Sedonovan already has.

    --
    Steve Schapel, Microsoft Access MVP
     
  6. JK

    JK
    Expand Collapse
    Guest

    Steve,

    I disagree, this is till required as a teacher can have more that class,
    Sedonovan specifically wants to be able to
    search for "all the classes they (the teachers) teach"

    Regards/JK
    (For Sherlock Holmes :)




    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:%23gC1Cf1rGHA.4844@TK2MSFTNGP05.phx.gbl...
    > JK,
    >
    > JK wrote:
    >> 1. TeachersClasses that have
    >> Teacher_ID
    >> Class_ID (preferable with the fields names as in your table
    >> (no need for any more fields)
    >> Mark both of them as Primary keys (this will ensure that you will not
    >> have a duplications of teacher-class combination twice or more

    >
    > I'm pretty sure this is not correct. This would only apply if a Class can
    > have more than one Teacher. Otherwise the Teacher goes in the Classes
    > table, which I believe is what Sedonovan already has.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
     
  7. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    JK,

    Sorry. If a teacher can have more than one class, there is a
    one-to-many relationship between Teachers and Classes. This should be
    modelled to a foreign key TeacherID in the Classes table. This will
    allow Sedonovan to use a query to return all the Classes for each
    Teacher, or all the Classes for any selected Teacher. Your suggestion
    of a TeacherClasses table is incorrect.

    --
    Steve Schapel, Microsoft Access MVP

    JK wrote:
    > Steve,
    >
    > I disagree, this is till required as a teacher can have more that class,
    > Sedonovan specifically wants to be able to
    > search for "all the classes they (the teachers) teach"
     

Share This Page