Simple newbie querie 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 |
+------+------+-----+

 

[Dennis Schmidt]

Hi Stefan,

My name is Dennis Schmidt. Thank you for using the Microsoft Newsgroups.

You link the three tables together in a query, pull down the fields that
you want to see in addition to the two fields in the Actor_Movies table.
Place your criteria under either of these two fields so that if you
provided a name for the movie, all actors would be listed. Or if you
provided an actor's name for the actor field, all movies would be returned.

I hope this helps! If you have additional questions on this topic, please
reply to this posting.

Need quick answers to questions like these? The Microsoft Knowledge Base
provides a wealth of information that you can use to troubleshoot a problem
or answer a question! It's located at
http://support.microsoft.com/support/c.asp?M=F>.

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.

Regards,
Dennis Schmidt
Microsoft Support