Here is a picture of the Summary report by Student
Are both reports based on the same join table between students and supervisors?
Both reports show records from the same table yes.
And that table is the join table?
I'm working from this assumed set of relationships:
Students----<Student_Supervisor>------Supervisors (your names may be different)
Students::__pkStudentID = Student_Supervisor::_fkStudentID
Supervisors::__pkSupervisorID = Student_Supervisor::_fkSupervisorID
If you add in this relationship:
Student_Supervisor::_fkStudentID = Student_Supervisor|StudentID::_fkStudentID
Where Student_Supervisor|StudentID is an added Tutorial: What are Table Occurrences? of Student_Supervisor
The following script steps could bring up the needed records in a new window specifying the second layout:
Go To Related Record [Show only related records; From table: Student_Supervisor|StudentID; Using layout: "ReportByStudent" (Student_Supervisor) ; new window ]
Sort Records [Restore; no dialog ]
Would open up your new window, only list the records for the student clicked and in the new window.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
ok this is a bit confusing, and you seem to have a field here that i dont. a Student ID inside my Supervisor table.
Here is what I have.
Table: Student Info
Table: Student GAA Funding
auto enter serial for each table
Student Info::Stu ID#
Student GAA Funding::Student GAA Funding ID#
Student Info::Stu ID# = Student GAA Funding::StuID#_fk
Student Info::Supervisor#1 = Supervisor::Name
I don't see how that works to link one student to more than one supervisor and a given supervisor to more then one student.
You are also linking supervisors by name, which is not a good idea. names are not unique, subject to data entry errors and subject to change when the person named changes their name.
I have 4 fields in each student record. Each of the 4 fields are drop down lists based on the field Supervisors::Name. This prevents data entry errors. The 4 fields are simply named Supervisor#1; Supervisor#2 etc. #1 is the primary and this field is used to allocate that student as under that one supervisor. i watched a video on how to use ID#'s but have the name show... would that be more appropriate?
The Supervisors names do change, if a student changes from a MSc program to a PhD program they will be a whole new set of Supervisors.
Are you suggesting a better way?
Yes I am suggesting a better way. That set of relationships is mapped out in my initial post of this thread.
I suggest at join table between supervisors and students linked as shown in that post. Both of your reports would then be created on layouts based on that join table.
Ok i created the new table "Student_Supervisor" and added the two fields "StudentID_fk" and "SupervisorID_fk" then made the following relationships
Student Info::Stu ID#-----<Student_Supervisor::StudentID_fk
then i created the table occurrence of Student_Supervisor and linked Student_Supervisor::StudentID_fk to Student_Supervisor|StudentIDr::StudentID_fk…. doesn't this create a loop?
But my reports are both created from Student GAA Funding table, and neither of the Student_Supervisor or the Supervisor tables are connected to the Student GAA Funding table…. so as far as i know i can't pull up a related record from them.
I must be missing something, my Student Info table is connected to the Student GAA Funding table…..
ok this is over my head i think.
I would use the Student GAA Funding table as the join table between supervisors and students. That's what I assumed that you had in the first place.
What i had before was
Student Info::Stu ID#--------<Student GAA Funding::StuID_fk
That was all i had, based on what i thought you wanted, i created a brand new table with only 2 fields in it which i linked like this:
Student Info::Stu ID#-------Student Supervisor::StudentID_fk
Student Supervisor::SupervisorID_fk--------Supervisor::Supervisor ID#
Then i made a table occurance for Student Supervisor and named it Student Supervisor_StudentID I linked Student Supervisor::StudentID_fk to Student Supervisor_StudentID::StudentID_fk.
this was confusing to me, i was linking the same fields to each other.
None of that included the Student GAA Funding table, it's still only connected to my main table, not the Supervisor table or the Student Supervisor tables.
And this is what I am suggesting that you change. Note that you GAA funding table has a field for supervisor and a field for student. So from here, it looks like you can set up that table to function as the join table. And I strongly recommend that you not link tables by name fields. Names are not unique, subject to change and vulnerable to data entry issues due to data entry errors. And all of these can create serious issues for related records as changing the name will disconnect that record from related records.
You mean like this:
Not quiite. There's no need for Student_Supervisor.