I have a main form that I display basic information about actors in my dvd collection. My main table has the primary key "ProgramID". My actors table has primary key "ActorID" and my Characters table has primary key "CharacterID".
On my main form there is a subform that displays what movies this actor has been in.I would like to add another field to this subform that displays what character this actor played in each of the movie selections displayed in the subform. Below is the record source for my subform:
SELECT DISTINCTROW Actors.*, main.ProgramID, main.title FROM main RIGHT JOIN (Actors LEFT JOIN ProgramActorJoin ON Actors.ActorID=ProgramActorJoin.ActorID) ON main.ProgramID=ProgramActorJoin.ProgramID;
The above code links my actor and movies together. This code works. I would like to add the character table to this code and link all 3 tables together, so when you display an actors record, the subform shows the movies the actors been in and also the characters. Any help would be greatly appriciated.
On my main form there is a subform that displays what movies this actor has been in.I would like to add another field to this subform that displays what character this actor played in each of the movie selections displayed in the subform. Below is the record source for my subform:
SELECT DISTINCTROW Actors.*, main.ProgramID, main.title FROM main RIGHT JOIN (Actors LEFT JOIN ProgramActorJoin ON Actors.ActorID=ProgramActorJoin.ActorID) ON main.ProgramID=ProgramActorJoin.ProgramID;
The above code links my actor and movies together. This code works. I would like to add the character table to this code and link all 3 tables together, so when you display an actors record, the subform shows the movies the actors been in and also the characters. Any help would be greatly appriciated.
