0
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 
      (SELECT d.[customer number] & d.[membership number] AS CustMemb
            , d.[customer number]
            , agg.[doc type]
            , d.[Open Amount]
        FROM  (SELECT [doc type]
                    , [customer number]
                    , d.[Open Amount]
               FROM   data
               WHERE  [doc type] = 'RU'
              ) agg
        INNER JOIN [data] d                                   
           ON  d.[customer number] = agg.[customer number]
        GROUP  BY d.[customer number] & d.[membership number]
                , d.[customer number]
                , agg.[doc type]
      ) AS sub
GROUP  BY [doc type]
        , [customer number]
        ,[Open Amount]
HAVING COUNT([customer number]) = 1

Hi All,

I am getting a parameter input of d.[Open Amount] when ran in MS Access.

Would anyone know what could be causing this

John Daly
  • 201
  • 1
  • 3
  • 8
  • Didn't you check the possibilities posted at your previous very [similar question](https://stackoverflow.com/questions/67916316/your-query-does-not-include-the-expression-open-amount-as-part-of-an-aggerate)? – Gustav Jun 17 '21 at 12:17
  • Related to your much [earlier question](https://stackoverflow.com/q/67822467/1422451), remove the `d` alias in innermost `d.[Open Amount]` which could be a typo. Also, the higher level `d.[Open Amount]` needs to be aggregated or should be in `GROUP BY` or MS Access will err out per above *similar question*. Please learn about aggregate queries in SQL (and avoid re-asking the same question). – Parfait Jun 17 '21 at 12:39

0 Answers0