Subqueries cannot be used in the expression SELECT VBA in MS Access

Question

I need your help once again. This my table [Venda]:

I need to return the value grouped by [Cod Col] of the sum of [QTD Venda] and the sum of [Valor Bonif] multiplied by [QTD Venda] if [QTD Venda] is greater than [QTD Min].

The code I'm using is this:

fonteBD = "SELECT SUM ([QTD Venda]) as TOTAL, [Cod Col], [Colaborador], SUM(IIF(TOTAL > [QTD Min], (TOTAL - [QTD Min]) * [Valor Bonif],0)) AS RESULT FROM [venda] GROUP BY [Cod Col], [Colaborador] ORDER BY 1 DESC"
EntradaBD.Open fonteBD, conectabd, adOpenKeyset, adLockReadOnly

However, the search throws this error:

Subqueries cannot be used in the expression (IIF(TOTAL > [QTD Min], (TOTAL - [QTD Min]) * [Valor Bonif],0))

Can someone please help me? Thank you

Answer 1

You need to calculate the sum of QTD Venda as a separate query. You're grouping by Colaborador and Cod Col to get the total for each group, but then are asking the query to match that grouped figure with individual QTD Min and Valor Bonif figures that aren't part of the original grouping.
I hope that made sense.

I think what you're after is:

SELECT 
  TOTAL, 
  [Cod Col], 
  T2.Colaborador, 
  IIF(TOTAL > [QTD MIN], (TOTAL - [QTD MIN])* [Valor Bonif], 0) AS RESULT 
FROM Venda INNER JOIN (
    SELECT 
      [Cod Col] AS CC, 
      Colaborador, 
      SUM([QTD Venda]) AS TOTAL 
    FROM Venda 
    GROUP BY 
      [Cod Col], 
      Colaborador
  ) T2 ON Venda.[Cod Col] = T2.CC  

or maybe:

SELECT 
  TOTAL, 
  [Cod Col], 
  T2.Colaborador, 
  SUM(IIF(TOTAL > [QTD MIN], (TOTAL - [QTD MIN])* [Valor Bonif], 0)) AS RESULT 
FROM Venda INNER JOIN (
    SELECT 
      [Cod Col] AS CC, 
      Colaborador, 
      SUM([QTD Venda]) AS TOTAL 
    FROM Venda 
    GROUP BY 
      [Cod Col], 
      Colaborador
  ) T2 ON Venda.[Cod Col] = T2.CC
GROUP BY TOTAL, [Cod Col], T2.Colaborador  

Edit following OP's comment so total QTD Venda is calculated separately, but individual QTD Venda is used in RESULT.

SELECT 
    TOTAL, 
    [Cod Col], 
    T2.Colaborador, 
    SUM(IIF([QTD Venda] > [QTD MIN], ([QTD Venda] - [QTD MIN])* [Valor Bonif], 0)) AS RESULT
FROM Venda INNER JOIN (SELECT 
      [Cod Col] AS CC, 
      Colaborador, 
      SUM([QTD Venda]) AS TOTAL 
    FROM Venda 
    GROUP BY 
      [Cod Col], 
      Colaborador
  )  AS T2 ON Venda.[Cod Col] = T2.CC
GROUP BY TOTAL, [Cod Col], T2.Colaborador;