0

I've found various examples of answers to questions very similar to mine. For some reason I can not get my query to work though.

What am I doing wrong?

I want to have the top 5 results returned with all other rows shown in the last row as "others".

Here is my SQL Syntax:

SELECT TOP 5 tbl_MopoRohdaten.rating_clir, Sum(tbl_MopoRohdaten.c2_eop_weight) AS SumOfc2_eop_weight
FROM tbl_MopoRohdaten
WHERE tbl_MopoRohdaten.rating_clir 
NOT IN 
    (EXISTS(SELECT TOP 5 tbl_MopoRohdaten.rating_clir, Sum(tbl_MopoRohdaten.c2_eop_weight) AS SumOfc2_eop_weight FROM  tbl_MopoRohdaten))
GROUP BY tbl_MopoRohdaten.rating_clir;

When I run it I get an error statement telling me:

You tried to execute a query that does not include the specified expression 'rating_clir' as part of an aggregate function.

UPDATE:

I've adjusted the code to reflect Balnian's input, but now Access (the entire application) simply shuts down and crashes whenever I try to run it!

SELECT TOP 5 tbl_MopoRohdaten.rating_clir, Sum(tbl_MopoRohdaten.c2_eop_weight) AS SumOfc2_eop_weight
FROM tbl_MopoRohdaten
WHERE tbl_MopoRohdaten.rating_clir 
NOT IN 
    (EXISTS(SELECT TOP 5 tbl_MopoRohdaten.rating_clir,Sum(tbl_MopoRohdaten.c2_eop_weight) AS SumOfc2_eop_weight 
FROM   tbl_MopoRohdaten GROUP BY y tbl_MopoRohdaten.rating_clir))
GROUP BY tbl_MopoRohdaten.rating_clir;
Community
  • 1
  • 1
rohrl77
  • 3,277
  • 11
  • 47
  • 73

1 Answers1

0

The select in the exist part of you query is missing the group by that is mandatory since you have a SUM() and a normal column returned in your query

EXISTS(SELECT TOP 5 tbl_MopoRohdaten.rating_clir,
Sum(tbl_MopoRohdaten.c2_eop_weight) AS SumOfc2_eop_weight FROM 
tbl_MopoRohdaten)

should be (not tested)

EXISTS(SELECT TOP 5 tbl_MopoRohdaten.rating_clir,Sum(tbl_MopoRohdaten.c2_eop_weight) AS SumOfc2_eop_weight 
FROM   tbl_MopoRohdaten group by tbl_MopoRohdaten.rating_clir)
Balnian
  • 11
  • 1
  • Thanks for your help. However, when I run the query now with the updated statement, Access crashes on me. It simply shuts down. No error message for SQL. Any idea why this might happen? I added an updated to my original post – rohrl77 Aug 14 '15 at 08:58