9ballrider said:
I am new to access, so I'm sure that this question will seem stupid and
quite
easy to any of you who read this, but i am trying to create my own
personal
movie database based on the DVD's in my collection. What i want to do is
add
actors and actresses by the movie title. I want to add several for each
movie. I also want to be able to reference what movies different actors
and
actresses have been in together. How can i set it up so that I can just
select the movie I want to add actors to, and how do i set up the query to
return all results in which an actor had appeared in a movie with an
actress
and what the movie(s) was/were?
You've jumped in at the deep end here, as this involves a many-to-many
relationship. That is, an actor or actress will usually appear in more than
one movie, and a movie will usually feature more than one actor or actress.
To model a many-to-many relationship like this, you need three tables, a
movies table, an actors table, and an intermediate linking table which, for
this example, I've called movies_actors. The many-to-many relationship
between movies and actors is implemented by establishing two one-to-many
relationships, one between movies_actors and actors, and one between
movies_actors and movies.
For example, my movies table has fields MovieId and MovieTitle, with MovieId
being the primary key. My actors table has fields ActorId and ActorName,
with ActorId being the primary key. My movies_actors table has fields
MovieId and ActorId, with a multi-field primary key including these two
fields. This means that the same actor can be added to the movies_actors
table more than once, and the same movie can be added more than once, but
the combination of an actor and a movie must be unique. There are, of
course, cases where an actor may play more than one role in a movie, but
that's a separate issue that I won't get into now, else this post will turn
into a book!
The relationships are one-to-many between movies_actors.ActorId and
actors.ActorId, and one-to-many between movies_actors.MovieId and
movies.MovieId.
Given this structure, one way to return the result you require is to include
the movies_actors and actors table in the query twice, once for each actor,
like so ...
SELECT movies.MovieTitle, actors.ActorName, actors_1.ActorName
FROM actors AS actors_1 INNER JOIN (movies_actors AS movies_actors_1 INNER
JOIN (actors INNER JOIN (movies INNER JOIN movies_actors ON movies.MovieId =
movies_actors.MovieId) ON actors.ActorId = movies_actors.ActorId) ON
movies_actors_1.MovieId = movies.MovieId) ON actors_1.ActorId =
movies_actors_1.ActorId
WHERE (((actors.ActorName)=[First Actor?]) AND ((actors_1.ActorName)=[Second
Actor?]));
