Showing indirectly related data
I have a table of book information - title, publisher, etc.
Each book may have multiple authors and/or editors.
It seemed to me to be good practice to create a table of Contributors to store the details of these people and then make a link table to tie them to the books with which they are associated.
So, I have Three tables (in this scenario):
The Contributions table had three fields when I first set the database up - the TitleID, the ContributorID (both numbers and being the foreign keys of the relevant tables) and the Role - a text field.
What I wanted to do on the layout was have a portal to display the unknown number of contributors - selecting those where the TitleID was the same as the current Title being displayed on the layout.
I ran straight into a problem - the portal won't let me show the Contributor's name because it's not in the Contribution record - only a reference to it is held there.
I have now added a fourth field to the Contribution record and made it a Calculation field with the result being:
I can add this field to the portal, no problem. However, when the layout is displayed, only the current line of the portal actually shows the name - if I click on one of the other lines, that name is displayed but the first one vanishes.
It must be possible to show all the names all the time, but I don't see what else I can do.
In reality, I'm told that no title will have more than 6 contributors (or perhaps we're just going to ignore the 7th and subsequent) so I could just have 6 fields on the title layout to show them but this seems a little untidy and not very good database practice.
I've attached a small screen shot to show the portal.
I'd be grateful for any pointers.