CODEWITHSUNDEEP
sqlms-access
Jeroen Wallbrink
Jeroen Wallbrink

Reputation: 71

Acces query works but when i try to make a report i get an error

I'm having trouble with an SQL query in Microsoft Acces. I made the query in MYSQL after that I have put it in Microsoft Acces. This all works fine. I get the information that i requested in my query. But when i want to make an report of it in acces it gives me this Error:

Group by clause with more levels in subquery is not allowed.

I'm seriosly like why?? It works everywhere in acces except in the report function...

This is the query:

SELECT I.genre_name AS Genre, round(Count(I.movie_id)*100/(select count(*) FROM MovieGenre)) AS Percentage
FROM MovieGenre AS I
GROUP BY I.genre_name
ORDER BY Count(I.movie_id) DESC;

Upvotes: 1

Views: 62

Answers (1)

Josh Miller
Josh Miller

Reputation: 620

Try changing your query to this:

SELECT I.genre_name AS Genre, round(Count(I.movie_id)*100/DCount("movie_id","MovieGenre"))  AS Percentage
FROM MovieGenre AS I
GROUP BY I.genre_name
ORDER BY Count(I.movie_id) DESC;

This Post has a good description of the "why".

Here is and MSDN article for DCount

Upvotes: 1

Related Questions