I do not see any join table in your data model and the relationship between Members and Shows will not work as the large number of match fields listed for Shows pretty much insures that no record in members will match to any record in shows.
There also seems to be a need for three basic tables in addition to any join tables as it would seem that you need a table for judges, a table for the bands/schools and a table for the shows at which the judges will be judging and at which the bands will be competing.
Start with these relationships:
Judges::__pkJudgeID = Join::_fkJudgeID
Shows::__pkShowID = Join::_fkShowID
You can place a portal to your join table on the Judges layout to list and select Shows for each given Judge. Fields from Shows can be included in the Portal to show additional info about each selected Shows record and the _fkShowID field can be set up with a value list for selecting Shows records by their ID field.
The Judge's position at a given Show can be recorded in a field in Join. A list view layout based on Join can be used to produce a list of Judges for a given Show. Such a layout can include all needed fields from both Judges and Shows.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
PS. used to play Trombone in HS marching band. Spent many a Saturday morning in uniform more years ago than I prefer to remember....
Many thanks for your help! I see now that what I had tried to do was a complete mess! I That got me doing more searching, and I think I understand things better, but here's my issue: I've been able to get data from Shows to appear in a layout in Join_Shows_Members. But as an example, if I select a member's name from a drop-down on a layout in Shows, called Chief Judge, I can get that same name to appear on the layout in Join-Shows_Members. What I need to do is get other fields relating to that same member to display on the same layout. For example, Cell Phone. I am trying to create a layout that allows me to select a show by name or date, and it will return all the members assigned to the assignment fields (like Chief Judge) and then also display those members' contact information. I can't for the life of me see that being generated by a portal because it calls information from two tables, but again, I think I'm missing something. I've been doing a lot of studying but this still eludes me...
As you can see above, I started to add the same kind of fields to the join table, thinking I would use that one to select judges for their assignments instead of in the Shows table. Would that work better? Basically, how do I pull information from other fields from the same record based on the selection of one field?
I would put a text field in the Join table and give it a value list that identified the judging assignment for that particular member's participation in a given show. So your value list would read: "Head Judge", "Music Ex 1", "Music Ex 2", ...
Please keep in mind that on a layout based on the join table, you can include fields from both members and shows on that layout. Once you have selected a member for that join table record by selecting a value in _fkMemberID, fields from Members on that layout will show data from the selected record in Members. The same holds for fields from Shows once you select a value for _fkShowID. This is also true if you use a portal to the join table when located on either the Members or Shows layout. So you could use a Shows layout with a portal to the Join table as a means of selecting judges for a given show.
You've been very kind and patient, and as a retired teacher I appreciate that a lot! I hope these questions are OK:
Why would I use a value list with the members' assignments (Chief Judge, Music Ex 1, etc.)? I don't see how that relates - it seems backwards to me. Each assignment can only be related to one judge's name, so it seems to me that selecting the member's name in a field that says "Chief Judge" would make more sense. I originally set up the assignments on a Show layout just like that. I can get those to display on a layout based on the Members_Join_Shows table. But what continues to be the issue is how to fetch the correct email address from the same members record. So if I select a member as Chief Judge, the email field should automatically populate with his/her email address from that record in Members. I can move the assignment selections to a layout based on the join table (I've tried that), but see the example below: What I tried to do to relate Members::Email Address to Shows::Chief Judge doesn't work. If I make change the Chief Judge field to Members_Join_Shows, I still can't relate the correct email address from Members.
I apologize again for my thickheadedness! I feel like what I need is right in front of me, and should be very simple, but I can't see it!
You are already linking a member to an event by creating a record in the join table to link the two. By adding a field in the join table to identify the role that they will have in your event, you use a single record to both link a member to the event and document what their responsibilities will be at that event.
A portal to the join table on the event layout an then list every official that will serve in that event. Change records to a different event and you'll see a the same portal listing the officials selected for that event. Other fields from members can be placed in that portal to show each official's contact info if such is necessary. This is all made possible by our data model--the tables and relationships we've already setup and no additional work or special scripting is needed.
If you put fields in the event record for selecting each judge for that event, you'd have to create multiple occurrences of the members table, each linked to a different one of these "official" tables. That's a much more complex data model all to achieve exactly the same result.
I wanted to follow up and thank you for all your help. I ended up doing it a little differently - once I got a portal working I set up a value table for all the judging captions, or "jobs," then selected the judge using a second TO of the members table. That seemed to do the trick. I think the problem was that I was trying to set up the portal on a layout using the wrong portal. and I got hung up on trying to set up a separate portal for each caption. It's working fine now, and I really appreciate your help! And it's never to late to get the trombone out again! :)