Query to count matching records from a junction table

[racefan91]

I have a movie database and I want to count how many times 2 actors worked on a matching movie. I have a tblMovies, tblActors and tblMoviesActorsJunction. I'm not sure if there is a way to do some type of crosstab query or report where I can count how many times 2 actors have worked together. Example would be actors Robert Downey Jr and Chris Evans, movies would be Avengers, Captain America: Civil War and Avengers: Infinity War. So I want to count those and list it as them working together 3 times. Any suggestions on how to do this would be appreciated.

[Minty]

Might need to see your Table structure to crack that, as I suspect it will require a number of sub-queries, to determine where any two actors have worked together to start with.
Will probably need some form of cartesian join.

[racefan91]

My tables look like this:

tblMovies has MovieID, MovieYear, MovieName
tblActors has ActorID, ActorName

I have a form where I enter the MovieYear and MovieName and it has a subform that adds the Actor. The subform is my junction table and has MovieID and ActorID.
My junction table displays like this

MovieID ActorID
Avengers Robert Downey Jr
Avengers Chris Evans
Captain America: Civil War Robert Downey Jr
Captain America: Civil War Chris Evans
Avengers: Infinity War Robert Downey Jr
Avengers: Infinity War Chris Evans

[Bob Fitz]

The subform is my junction table and has MovieID and ActorID.

A subform is a form. Not a table. It may be bound to a table/query. What is the name of the table/query that it is bound to.

[racefan91]

tblMoviesActorsJunction

[ArviLaanemets]

On fly:

SELECT MovieList.MovieID, m.MovieName, m.MovieYear, MovieList.MoviesCnt
FROM
     (
     SELECT ma.MovieID, COUNT(ma.MovieID) As MoviesCnt
     FROM (tblMoviesActorsJunction ma INNER JOIN tblActors a1 ON a1.ActorID = ma.ActorID) INNER JOIN tblActors a2 ON a2.ActorID = ma.ActorID
     WHERE a1.ActorID = [FirstActorID] and a2.ActorID = [SecondActorID]
     GROUP BY ma.MovieID
     ) MovieList INNER JOIN tblMovies m ON m.MovieID = MovieList.MovieID

This probably lists all movies those 2 actors participated in, without their names.