Geek asks and answers

GROUP BY multi-level clause not allowed in subquery

I have a request in MS Access

SELECT tblUsers.Forename, tblUsers.Surname, 
  (SELECT COUNT(ID) 
     FROM tblGrades 
     WHERE UserID = tblUsers.UserID 
     AND (Grade = 'A' OR Grade = 'B' OR Grade = 'C')) AS TotalGrades
FROM tblUsers

I put this in the report, and now, when you try to view the report, it displays the warning " Multilevel GROUP BY clause is not allowed in a subquery "

What I am not getting is that I don’t even have any GROUP BY clauses in the query, why does it return this error?

+3
source share
3 answers

Allen Browne's Great Access Tips Website: Surviving Subqueries

Error: "Multilevel group not allowed"

, . , . ?

, Access " ". "" .

  • " " - . ( .)

  • . ( , .)

  • , . "" , . ( ) , ,

    SELECT * FROM Query1;

  • , ​​ DSum() . , .

  • , . Append ( Query ), , .

: , , Allen Browne . -:

< > - (, , ,...) (, , , ,...). , , ​​ , , : : http://allenbrowne.com' : http://allenbrowne.com

+8

:

SELECT users.Forename, users.Surname, grades.TotalGrades
FROM tblUsers AS users
LEFT JOIN (SELECT COUNT(ID) as TotalGrades, UserID FROM tblGrades WHERE (Grade = 'A' OR Grade = 'B' OR Grade = 'C') group by userid) AS grades on grades.UserID = users.UserID

. , , .

+3

:

SELECT users.Forename, users.Surname, count(grades.id) AS TotalGrades
FROM tblUsers AS users
INNER JOIN tblGrades AS grades ON users.ID=grades.UserID
WHERE grades.Grade in ("A","B","C") group by users.ID;

This is a simple join table. It basically means. Select all cases where the user has a rating of “A” or “B” or “C” (which will give you a table like this:

   user1 | A 
   user1 | B 
   user1 | A 
   user2 | A 
   ...

And then he groups it by users, counting how many times the class → appeared, giving you the number of ratings in the right range for each user.

0
source

More articles:


All Articles