information from two records in table to one portalrow in another table

[kensje]

I'm working on a database with, from left to right, the following tables (and TO's) bound with relationships:

teachers - programming - course - enrolment - student

In teachers I have connected (through programming) a teacher to a course. And in students I have connected a student (through enrolment)to a certain course.

I have astablised a portal with information from teachers in student. This shows the teacher that is leading a certain course. So far so good. But there are courses who have more than one teacher. I want to show in the portal in student all the teachers who are assigned to a certain course. How do I do that?

-- JK

Edited December 7, 2005 by Guest

[Breezer]

From what I can see in your set-up, you must also have in STUDENT a foreign key for the course (fk_course_ID) in your STUDENT table. In your TEACHERS table, there is also a foreign key to courses (fk_course_ID). If this is true, then simply create a new TO of your TEACHERS table and link it to the STUDENT table using the course ID key (fk_course_ID).

In a nutshell, you need the following for this set-up to work efficiently:

STUDENTS table

pk_student_ID

fk_course_ID

other pertinent fields

TEACHERS table

pk_teachers_ID

fk_course_ID

other pertinent fields

COURSES table

pk_course_ID

other pertinent fields

Edited December 7, 2005 by Guest

[kensje]

My situation is a little different right now considering splitting up many to many tables:

-- Teachers --

pk_teacher_ID

other fields

-- Programming --

pk_programming_ID

fk_teacher_ID

fk_course_ID

-- course --

pk_course_ID

other fields

-- enrolment --

pk_enrolment_ID

fk_student_ID

fk_course_ID

other fields

-- student --

pk_student_ID

other fields

What I try to accomplish in student is showing in a portal the name and ID of the course (from course through enrolment) and one or two names of teacher(s) from the teacher table. Each teacher has it's own record in the teacherstable.

So two teachers can by assigned to one and the same course and one student takes one course with two teachers.

-- JK

[Breezer]

ok, I see what you are trying to do. You can attach your file and I will try to implement a solution. I believe Jim McNeely of New Century Data calls it TELEPORTING.

http://www.newcenturydata.com/downloads/teleport.fp7

[Ender]

What I try to accomplish in student is showing in a portal the name and ID of the course (from course through enrolment) and one or two names of teacher(s) from the teacher table.

This can be accomplished by using a portal of related Programming records.

However, since each Student can have more than one Enrollment record, you would see all Teachers for all Courses for all the student's Enrollments if the portal is used in a layout based on the Student table. It may be better to place such a portal in a layout based on the Enrollment table so only the Teachers of the Courses for the selected Enrollment are shown. Alternatively, another set of table occurences of Teacher, Programming, and Course, that are filtered by the current enrollment year (or whatever) might be used to show only the current Enrollment's teachers.