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.
What tables and relationships do you have in place now? From there we can suggest changes, if needed to your structure without having to guess as to what you currently have in place that may work without making any such changes.