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?

"9ballrider" <(E-Mail Removed)> wrote in message
news94DF52C-0C23-4396-BECB-(E-Mail Removed)...
>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?]));

--
Brendan Reynolds