Hi,
I am fairly new to using MS Access 2010, on Windows 7.
I have a problem which I have been unable to solve by checking on google and watching several hours of tutorials.
My database covers my DVDs and Blu-Ray movies. It comprises four tables, tblMovies, tblActors, tblDirectors and tblProducers. In the tblMovies there are, among others, the following columns: Actor01ID, Actor02ID, Actor03ID, Actor04ID, DirectorID and ProcucerID.
I am able to form a relationship between the tblMovies, tblDirectors and tblProducers quite easily, but when I want to relate the tblActors to Actor01ID, Actor02ID, Actor03ID, and Actor04ID in the tblMovies, I cannot get it to work. The plan was that the tblActors contains all of the actors and actresses in all of my movies, and Actor01ID, Actor02ID, Actor03ID, Actor04ID are the Male Leading Actor, the Female Leading Actor, Supporting Actor Number One and Supporting Actor Number Two.
However, if I relate tblActors Primary Key to Actor01ID, I am unable to relate tblActors Primary Key to Actor02ID, etc.
I have looked into the many-to-many relationships using a joint table, but that does not seem to do the job.
Please advise me how to create the relationships that I am looking for.