Your Query Does Not Include The Expression 'open Amount' As Part Of An Aggerate Function - Ms Access

SELECT [doc type], [Open Amount] , [customer number] , COUNT([customer number]) As CountCustomerNumber , SUM(IIF([Open Amount]>'0', [Open Amount], 0)) AS sum_open

Solution 1:

See the following stackoverflow post: “You tried to execute a query that does not include the specified aggregate function”. I think you just need to add [Open Amount] to the final GROUP BY

  .
  . 
  .
GROUPBY [doc type]
        , [customer number]
        , [Open Amount]
HAVINGCOUNT([customer number]) =1

???

Solution 2:

You have three elements in you select that are not part of any aggregation but only two elements in your group by, that is why you get the error message.

When you use aggregation function, all the non aggregated elements of the select need to be in the group by hence your query become like this :

EDIT : You are selecting [Open Amount] and aggregating SUM(agg.[Open Amount]) AS SumOpenAmount in your sub query.

You are also doing something similar in your external query

SELECT [doc type], [Open Amount]
     , [customer number]
     , COUNT([customer number]) As CountCustomerNumber
     , SUM(IIF([Open Amount]>'0', [Open Amount], 0)) AS sum_open_amount_pos   
     , SUM(IIF([Open Amount]<'0', [Open Amount], 0)) As sum_open_amount_neg         
FROM SubQueries
GROUPBY [doc type]
          [Open Amount]
        , [customer number]
HAVINGCOUNT([customer number]) =1

Should be something like that :

SELECT [doc type]
         , [customer number]
         , COUNT([customer number]) As CountCustomerNumber
         , SUM(IIF([Open Amount]>'0', [Open Amount], 0)) AS sum_open_amount_pos   
         , SUM(IIF([Open Amount]<'0', [Open Amount], 0)) As sum_open_amount_neg         
    FROM SubQueries
    GROUPBY [doc type]
            , [customer number]
    HAVINGCOUNT([customer number]) =1

Solution 3:

When you sum over[Open Amount], you cannot also select each separate "Open Amount". So remove it from both "SELECT" and "GROUP BY":

SELECT [doc type] --, [Open Amount]
     , [customer number]
     --, COUNT([customer number]) As CountCustomerNumber
     , SUM(IIF([Open Amount]>'0', [Open Amount], 0)) AS sum_open_amount_pos   
     , SUM(IIF([Open Amount]<'0', [Open Amount], 0)) As sum_open_amount_neg         
FROM-- snipGROUPBY [doc type]
        , [customer number]
        -- , [Open Amount]-- snip

And similarly, if you group by "Customer Number", a Count([Customer Number]) would return 1 (if it worked).