Finding the names and the year of the movies that have at least 5 actors Given database of the website "eMovies" that manage data about cinema movies. 


  Write in SQL query that finds the names and the year of the movies that have at least 5 actors.




My attempt:

The idea: to do a natural joint between "Movies" and "ActorsMovies" tables, and then to count the rows with the same MovieID, and then to check if count>5

I succeded to count the rows thank's to @Phil's advice, but still I need the name of the movies and the year,

Here is my start: 

SELECT DISTINCT Year, MovieName
FROM Movies, ActrorsMovies
SELECT Count(ActorsMovies.MovieID) 
FROM ActorsMovies
GROUP BY ActorsMovies.MovieID
HAVING (((Count(ActorsMovies.ActorID))&gt;5)); 


This code does not work



EDIT: another attempt

 SELECT DISTINCT Movies.Year, Movies.MovieName
 FROM Movies, ActorsMovies
 GROUP BY ActorsMovies.MovieID
 HAVING Count(ActorsMovies.ActorID)&gt;5;
This code works in T-SQL and should work in Ms Access:

SELECT COUNT(M.MovieID) as 'Count', M.MovieName, M.Year FROM Movies M
INNER JOIN ActorsMovies AM ON AM.MovieID = M.MovieID
INNER JOIN Actors A ON AM.ActorID = A.ActorID
GROUP BY M.MovieID, M.MovieName, M.Year
HAVING COUNT(M.MovieID) &gt; 5


I also recommend not to use Year as a field name because it's a reserverd keyword in Ms Access and may cause problems:
https://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx