Newbie question

[Stefan Fredriksson]

I have three tables, Movies, Actors and Movies_Actors (see below).

One movie has many actors and one actor can be in many movies.
The Movies_Actors table is to "glue" the other two tables together.
Movie 1 has actors 1, 2 and 3. Movie 2 has actors 2 and 3 and
Movie 3 has only actor 3.

Now, what I want to do is to be able to do a search to get all the
movies that Actor 1 is in for example. Also to be able to get all
the actors in one certain movie.

Ccould anyone please help me with this?

The mdb file can be found here:
http://w1.351.telia.com/~u35111589/videotek.zip

if anyone would like to download it.

Thanks in advance

+-----------------+
| MOVIES |
+-------+---------+
|#artnr | Title |
+-------+---------+
| m1 | Movie 1 |
| m2 | Movie 2 |
| m3 | Movie 3 |
+-----------------+

+----------------+
| ACTORS |
+-------+--------+
|#idnr | name |
+-------+--------+
| a1 | Actor 1|
| a2 | Actor 2|
| a3 | Actor 3|
+----------------+

+-------------------+
|MOVIES_ACTORS |
+------+------+-----+
|#idx |*artnr|*idnr|
+------+------+-----|
| 1 | m1 | a1 |
| 2 | m1 | a2 |
| 3 | m1 | a3 |
| 4 | m2 | a2 |
| 5 | m2 | a3 |
| 6 | m3 | a3 |
+------+------+-----+

 

[Gabriela]

You need to create a query. Select QUERY-NEW-DESIGN VIEW.
A window shows up and select tables Movies, Actors and
Movies_Actors. Link (glue)them by the IDs. To do this
click on one table ID field and drag it to the other
tables ID field.
To know in which movies actor 1 has acted in:
Then click on the Movie tables, select the ID field and
drag it to the bottom portion of the query. Select Movie
title field from the same table. Then from the Actors
table select Actors ID, and drag it to bottom part of
query.
In the row where it says criteria, type: like "1" under
the Actors ID. Go to Menu Query,Run and you get the
answer. You can only view it or you can click on the top
left square of the answer table, copy and paste in an
excel spreadsheet. If you want to create a table with the
answer go to Menu Query Make Table Query and select a
name for the new table.
Hope this helps you out.

 

[John Vinson]

Now, what I want to do is to be able to do a search to get all the
movies that Actor 1 is in for example. Also to be able to get all
the actors in one certain movie.

Create a Query joining ALL THREE tables: Movies joined to MoviesActors
by the MovieID, and MoviesActors joined to Actors by ActorID.

You can now put a criterion on any field in any of the tables and find
all the records.

 

[Stefan Fredriksson]

Hi Gabriela,

thanks for your reply. Im sorry but I did not explain very well.
I have made the relationsships between the tables but I still can not
make the query correct. I am trying something like this:

SELECT title, name
FROM movies, Actors
WHERE Movies_Actors.idnr='1' AND Movies_Actors.artnr=Movies.artnr;

I have tried diffrent variations of the above but still cant seem
to get it right.

Regards
Stefan

Gabriela wrote

 

[Stefan Fredriksson]

Create a Query joining ALL THREE tables: Movies joined to MoviesActors
by the MovieID, and MoviesActors joined to Actors by ActorID.

You can now put a criterion on any field in any of the tables and find
all the records.

Hi John,
Thanks for your reply.

I have created the relationships but I cant seem to get the query
corretct. I have tried things like:

SELECT title, name
FROM movies, Actors
WHERE Movies_Actors.idnr='1' AND Movies_Actors.artnr=Movies.artnr;

that I thought would list all movies where Actor 1 is in.

Perhaps you could help me with the SQL code?

Regards
Stefan

 

[Shelly Jackson]

Hi Stefan:

I'm just a newbie too, but I couldn't help notice in your example you have a
field called "name" in the Movies table. Sorry, if I am wrong, but if you
do have a field called "name" I think you need to change it to something
like "moviename." Access does not like it when you name a field "name." It
gets confused.

Sorry I cannot help with the request of your question.

S. Jackson

 

[John Vinson]

Perhaps you could help me with the SQL code?

Sure:

SELECT Movies.*, Actors.*
FROM Movies INNER JOIN MoviesActors
ON Movies.MovieID = MoviesActors.MovieID
INNER JOIN Actors
On MoviesActors.ActorID = Actors.MovieID
WHERE ActorLastName = [Enter actor's last name:];

In the Query Grid, you would add all three tables, and make sure that
there is a join line between Movies and MoviesActors, joining MovieID
to MovieID, and the same with MovieActors and Actors.