Relationship organization

[gene.seeker]

I want to keep track of student enrollment in courses.

I have three tables: STUDENT, JOIN, and COURSES.

I have a portal in a STUDENT layout so I can see what they've taken.

I want to eventually do statistics on the student enrollments to determine when they take certain courses.

Currently lines 1-7 of the portal contain Fall Freshmen courses, lines 8-14 contain Spring Freshmen courses, etc.

The find function lets me find students who have taken certain courses (say, Chemistry). But the find returns ALL records where any of the portal rows contain Chemistry. What I want to know is whether portal rows 1-7 or 8-14 contain a Chemistry entry. Can I do finds for specific portal rows?

OR

Should I change my setup and include a new table called ENROLLMENTS. Enrollments will be between STUDENTs and JOIN, and then each student will have 8 enrollment tables (one for each semester). THEN I create a layout showing STUDENT records listing all enrollments. And if this is the case, I could probably have 8 portals on the student record (one for each semester). This way I can search for specific enrollments. Hope this is making sense. If you are used to the business setup, STUDENTs corresponds to CUSTOMERS; ENROLLMENTS corresponds to INVOICES, JOIN is LINES and COURSES is PRODUCTS.

Any advice from more experienced programmers would be much appreciated.

[comment]

I think your existing JOIN table **is** the Enrollments table. But it needs to have a field to record the semester, too. Portal row numbers are meaningless.

[gene.seeker]

Yes, that appears to work!

Now I have to run searches in the JOIN table, which will ultimately not be a problem, but it's not so intuitive to setup because all the JOIN layout shows is the foreign key for the course, not the course name. And coursename isn't even a field in the JOIN table. Eventually this will all be scripted but right now is there an easier way to lookup the coursename in the COURSE table that the foreign key in the JOIN table. Seems like I need another portal in each JOIN record??

[comment]

You can put the course name field from Courses in the portal.

[gene.seeker]

I did figure that out and now it looks great. Thank you.