Windows 7 64bit 
Access 2007
I have an MS Access 2007 Database with the following tables and attributes:
- Characters: Name
- Planets: Name
- TimeTable: Planet's Name, Character's Name, Movie
Time Table denotes if a character has visited a planet, there may be multiple entries for one planet.
The query I'm trying to get is this one:
For each movie, which characters visited the highest number of planets?
This is my attempt:
Quote:
SELECT T.Movie, T.[Character's Name], Count(T.[Planet's Name]) AS planets
FROM TimeTable T
GROUP BY T.Movie, T.[Character's Name]
HAVING Count(T.[Planet's Name]) >= ALL (SELECT Count(T2.[Planet's Name])
FROM TimeTable T2 WHERE T.Movie = T2.Movie);
It gives me an empty result though. What is wrong with my query?
Reply With Quote
