I have a database I'm trying to update from Microsoft Access. It is for a Student financial aid application. There is a table for students, and a related table for Student Profiles. A student profile is information regarding the course the student is taking, the fees, expenses, etc. The tables are linked by student ID. The layout pictured below is based on the Students table, and there is a self-join table to Students to power the student portal in upper left. When I click an item in the portal, it does a GTRR and displays the student info on this layout. This is working just fine. My aim is to also have a profiles portal (see upper middle area). If the student has more than 1 course of study, they will all show up in this portal.
If the user clicks on a profile record, the selected profile information should show on the bottom area of the layout. In the sample below, I only have the first few fields included.
The problem is that using a self-join table and GTRR will not work because the layout is based on students, not student profiles. It returns an error that the layout doesn't have fields for that table. If I change the layout to be based on student profiles instead, the right-hand portal works but then the left-hand students portal returns that error. So apparently you can have fields from another table, but can't use GTRR for this.
So I'm trying to find a work-around. I'm not averse to basing the layout on a different table, and using SQL for the portals. I do need to be able to pick a student from the left portal, and show a list of their programs (profiles) on the right portal, then choose one of those to display the profile information on the layout itself.
Can somebody give a suggestion on how to accomplish this? Thanks...