Listing Courses

[Colin]

I have a table, which is a list of courses with a CourseID key field and
about a dozen fields describing the courses. I also have a table that lists
all of the students taught by one teacher, which also has a field called
CourseID, which is linked to the CourseID in the other table. There will be
many students in each course and a teacher will teach more than one course.
What I want to do is to create a recordset that lists the courses taught by
that teacher, along with their included description fields. I am a bit
unsure how to get the list. For example, Teacher A teaches 3 courses, each
course having up to 15 students. This information is in one table with an
individual entry for each student - called "StudentInfo". The other table,
called "CourseInfo", consists of maybe 50 courses, with a dozen fields
accompanying them. What I am trying to extract, is just the list of the 3
courses with the fields accompanying them.

[Pavel Romashkin]

I am a little confused about your table structure, but I could think of
two ways you could have it set up. To extract just the list of the 3
courses with the fields accompanying them, try

SELECT * FROM CourseInfo INNER JOIN TeacherInfo ON CourseInfo.TeacherID
= TeacherInfo.TeacherID WHERE TeacherInfo.TeacherName = "A"

or

SELECT * FROM CourseInfo (INNER JOIN StudentInfo ON StudentInfo.CourseID
= CourseInfo.CourseID) INER JOIN TeacherInfo ON TeacherInfo.TeacherID =
StudentInfo.TeacherID WHERE TeacherInfo.TeacherName = "A"

Pavel

Colin wrote:

I have a table, which is a list of courses with a CourseID key field and
about a dozen fields describing the courses. I also have a table that lists
all of the students taught by one teacher, which also has a field called
CourseID, which is linked to the CourseID in the other table. There will be
many students in each course and a teacher will teach more than one course.
What I want to do is to create a recordset that lists the courses taught by
that teacher, along with their included description fields. I am a bit
unsure how to get the list. For example, Teacher A teaches 3 courses, each
course having up to 15 students. This information is in one table with an
individual entry for each student - called "StudentInfo". The other table,
called "CourseInfo", consists of maybe 50 courses, with a dozen fields
accompanying them. What I am trying to extract, is just the list of the 3
courses with the fields accompanying them.

[Pieter Linden]

I agree with Pavel... I had to do this kind of thing for a class... I
think the best structure might be something like this:

Department--(1,M)--Course--(1,M)--ClassSection-(1,M)-SectionRoster--(M,1)--Student

Then Instructor would be related to ClassSection (1,M)

ClassSection(
CourseID, (FK from Course)
ClassSectionID, (Primary Key)
InstructorID, (FK from Instructor)
RoomNo,
StartTime,
FinishTime,
....)

SectionRoster(
ClassSectionID,
StudentID,
Grade...)

If you do it this way, it's a lot more flexible, and you can get
pretty much any information you want without having to resort to union
queries etc.

HTH,
Pieter