i get error when i try to run query in access through C#

Question

hi

i have this query:

SELECT DO3Tbl.CodeDip, DO3Tbl.BarcodeD, Sum(DO3Tbl.Qty) AS Qty,
       Max(DO3Tbl.Mly) AS Mly, [Qty]-[Mly] AS Tot, ABS(Tot) AS TotAbs

when i run this query in access its work's excellent

but when i run this query in C# code like this:

SQL =     SELECT DO3Tbl.CodeDip, DO3Tbl.BarcodeD, Sum(DO3Tbl.Qty) AS Qty,
          Max(DO3Tbl.Mly) AS Mly, [Qty]-[Mly] AS Tot, ABS(Tot) AS TotAbs 
Cmd = new OleDbCommand(SQL, Conn);
            Cmd.ExecuteNonQuery();
            Cmd.Dispose();

i get this error:

You tried to execute a query that does not include the specified expression '[Qty]-[Mly]' as part of an aggregate function.

Answer 1

I propose that instead of counting the

Tot and TotAbs

values directly in the query.

It's better to count them in the code since we are able to avoid any issue related to the query execution order in the SELECT clause.

Hence your query would be like that:

SELECT DO3Tbl.CodeDip, DO3Tbl.BarcodeD, Sum(DO3Tbl.Qty) AS Qty,
       Max(DO3Tbl.Mly) AS Mly

Cheers.

Answer 2

There is quite a lot wrong with the query as far as Access is concerned, for example, you are not selecting from any table, also, you cannot use an Alias that is the sames as a field name. How about:

SELECT DO3Tbl.CodeDip, DO3Tbl.BarcodeD, Sum(DO3Tbl.Qty) AS SumOfQty, Max(DO3Tbl.Mly) AS MaxOfMly, Sum([Qty]-[Mly]) AS Tot, Abs(Tot) AS TotAbs
FROM DO3Tbl
GROUP BY DO3Tbl.CodeDip, DO3Tbl.BarcodeD;