Can't get many-to-many relationship to work

[radiodave]

I don't know how to ask this question but I'll try:

I'm making a database (Access 2003) for an Opera buff. I have tables related like:

each Opera has many Productions (Madame Butterfly has an SF Opera production)
each Opera has many Roles (Madame Butterfly has Cio-Cio-San)
each Role has many Persons (Cio-Cio-San is played by Patricia Racette)

But the Person playing the Role depends on the Production. So, just like the program you get at the opera, I have a table Cast. Cast has foreign keys from Opera, Role, and Person.

I can't get my Cast queries to behave. I'm not surprised, because Cast is stuck in the middle and Opera is loose at the ends:

Opera > Production > Cast < Role < Opera

Not a very technical description but the relationship doesn't feel right. Cast is sort of fixing the many-to-many relationship between Production and Role (each Production has many Roles and each Role appears in many Productions). But having the same Opera table on the one side of both Production and Role I think messes things up.

If you understand what I'm trying to do and see a better way to do it, please let me know. Thanks!

[MMcCarthy]

tblOpera
OperaID (Primary Key - Autonumber)
OperaName

This table will contain a unique list of Operas

tblSinger
SingerID (Primary Key - Autonumber)
SingerName

Each singer will only appear once in this table.

tblProduction
ProductionID (Primary Key - Autonumber)
ProductionName
ProductionDate
OperaID (Foreign Key referencing the Primary key of tblOpera)

Each Production will be listed in this table.

tblRole
RoleID (Primary Key - Autonumber)
RoleName
OperaID (Foreign Key referencing the Primary key of tblOpera)

Each role will be listed in this table and reference the opera it's in.

tblCast
CastID (Primary Key - Autonumber)
SingerID (Foreign Key referencing the Primary key of tblSinger)
RoleID (Foreign Key referencing the Primary key of tblRole)
ProductionID (Foreign Key referencing the Primary key of tblProduction)

Now the cast consists of a singer, the role and the production.


Mary