Displaying a report which shows cast name and their role (many cast name to the same role)?
I am trying to build a database which will allow me to store a play, i.e the play would be called 'Hadrian's Wall' the play will store all the roles in the play, i.e:
However their are multiple performances of that play for which the role played will have a different actor i.e:
Played by: Henry, Julian, Smith
How can I generate a report which will show all the cast members of all the plays and the relevant role? Obviously I could store the role name with the actors name but that would cause duplication and ultimately I am looking to have only one instance of this data. So it is a one (Role Name) to many (Actor Name)
I am happy to change the structure of my database in any way to accomodate this feature. I.e the report would look like this:
Role Name - Actor Name
Record 1 Hadrian Henry
Record 2 Hadrian Juiian
Record 3 Hadrian Smith
Really appreciate any help that someone might be able to give me.