What you have set up requires three different relationships between lectures and lecturers:
LectureSchedule::FacID1 = LectureFaculty1::FacultyID
LectureSchedule::FacID2 = LectureFaculty2::FacultyID
LectureSchedule::FacID3 = LectureFaculty3::FacultyID
LectureFaculty1, LectureFaculty2 and LectureFaculty3 are all table occurrences of the same data source table.
There is a better approach, however and one that won't limit you to a maximum of three lecturers:
LectureSchedule::LectureScheduleID = Schedule_Faculty::LectureScheduleID
Faculty::FacultyID = LectureSchedule::FacultyID
You then place a portal to Schedule_Faculty on your lecture schedule layout.
Put Schedule_Faculty::FacultyID plus faculty name and any othe fields from Faculty that you need to list the lecturers assigned to that LectureSchedule record. Format the Faculty ID field as a drop down list or pop up menu of Faculty IDs and names so you can use it to assign lecturers from the faculty table.
Thanks Phil. I will try that.
Are there any major structural advantages to the third table method other than the ability to have more than 3 lecturers? I mean for scripting and reporting purposes? I did consider that approach but I have so many different related tables and portals for many other types of data that one more felt a bit overwhelming. I'm pretty certain that there will never be a 4th lecturer, 2 is not so uncommon but 3 is extremely rare. Actually, when I though about reporting, the 3 fields seemed a better option.
The flexible number of lecturers that become possible is an additional benefit, not the main reason for doing this. Note that you need three different relationships for your three fields so the join table approach is simpler than three different relationships. (2 table occurrences with the Join table method and 3 with the separate field method.)
Now consider if you want to see all lectures scheduled for "John Smith". With three fields, you have to search all three fields. With the portal, you need only search one.
If you want to print out a report listing all of John Smith's scheduled lectures, you can easily do so from a layout based on the join table--in fact you can list a report for all lecturers, with scheduled lectures listed under each person's name. Can't do either of those when you have three separate fields.
If your Relationship map is getting too busy, you might investigate using the Anchor Buoy method for bringing some order to the chaos.
That's true, I hadn't really been thinking about reporting by faculty. Most of the reporting is on the lecture table and those have several relationships (to an objectives table, a teaching unit table, keywords table, exam questions table, etc... So I was trying to put as much of the the lecture specific stuff on the lecture table since I can't report, for example, on both the relationship between lecture and question and between lecture and faculty on the same report. Any ideas?
Also, can you double check the Anchor Buoy link? I'm getting a 404 error.
Try the link now, I've fixed it. (Should have double checked what actually got pasted in to the link editor.)
I don't see why you can't report on lecture, question and faculty in the same report.
If you have these relationships:
Lecture::LectureID = Questions::LectureID
Lecture::LectureID = LectureSchedule::LectureID
Faculty::FacultyID = LectureSchedule::FacultyID
Then you can base your report on either Questions or Faculty and list either the Faculty or Questions records in a portal with LectureSchedule fields added directly to the layout.
Really? I was under the impression that portals did not work for reports. How do you make them shrink or grow?
Would appreciate any links to useful threads or tutorials on using portals in reports.
If that works. I think I will go with the third table approach.
As usual, thanks for all your expertise!
Portals are a good thing to avoid in reports, but sometimes you can't easily avoid them.
Portals can indeed shrink/slide up in a report as a unit, but fields inside the portal cannot slide up. Thus, you can put a 10 row portal on your layout and have it shrink to a 5 row portal if you only have 5 records in the portal, but you can't make a two line field inside of the portal slide and shrink to only one line height if there's only one line of text in the field.
Thus, a small portal listing your faculty should work pretty well here, but a portal of Exam Questions might not.
It's possible to do away with the portal, but with the relationships that we have here, you end up creating a temporary report table and importing data into it just so you can list all the data in correctly grouped format.
This issue does not occur when you have these relationships:
When it's a linear one to many from table to table like this, you can base your layout on GrandChildren and then include fields from Children and Parent on the same layout--often in grand summary, sub summary, header, and/or footer layout parts.