linked records/relationships

[sedonovan]

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

)

 

[Steve Schapel]

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

 

[JK]

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

 

[Steve Schapel]

JK,

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.

 

[JK]

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]

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.