Aggregate Query Help

[eerich]

I'm having trouble with an aggregate query where I need to return the results of all items where the absolute value is not = 0. Currently the results I recieve just returns all items by document number. Here's my query:

SELECT documentno AS JENumber, count(documentno) AS NumofLineItems, (round(sum(GlAmount),2)) AS AbsoluteValue, sum(IIf([dc]="s",glamount,0)) AS Debits, sum(IIf([dc]="h",glamount,0)) AS Credits
FROM gltable
GROUP BY documentno
HAVING (((((sum(IIf([dc]="s",glamount,0)))-(sum(IIf([dc]="h",glamount,0)))))<>0));

I think the error is in the Having clause, but I've tried various things and can't the correct results. Any help is appreciated.

 

[r937]

trouble? error? what sort of error messages are you getting?

r937.com | rudy.ca

 

[Golom]

Just a guess but debits and credits often (depending on the system) have opposite signs.

Perhaps your test should be

HAVING Abs(sum(IIf([dc]="s",glamount,0)) <> Abs(sum(IIf([dc]="h",glamount,0))

 

[eerich]

I'm not receiving an error message, but the query results are incorrect. I have the amount field (glamount) and a debit/credit indicator field (drcr). The data is from an SAP system. The results i'm trying to get is for the query to return the the lines where the sum of the debits minus the sum of the credits for the specified document number does not net to zero. So for instance, using the following data:

DocNum Amount DrCr
0001 10.00 D
0001 04.00 C
0001 06.00 C
0002 15.00 D
0002 10.00 C

My query:
SELECT docnum, count(docnum) AS NumofLineItems, (round(sum(Amount),2)) AS AbsoluteValue, sum(IIf([drcr]="D",amount,0)) AS Debits, sum(IIf([drcr]="C",amount,0)) AS Credits
FROM myTable
GROUP BY docnum
HAVING (((((sum(IIf([drcr]="D",amount,0)))-(sum(IIf([drcr]="C",amount,0)))))<>0));


The results should be:

DocNum NumofLines AbsoluteValue Dr Cr
0002 2 5.00 15.00 10.00

Any help is appreciated.

 

[eerich]

In the above data I listed the Credit items would have a negative sign.

DocNum Amount DrCr
0001 10.00 D
0001 -04.00 C
0001 -06.00 C
0002 15.00 D
0002 -10.00 C

Thanks.

 

[Golom]

Does not compute

Given your sample data and your SQL, the results should be
[tt]
DocNum NumofLines AbsoluteValue Dr Cr
0002 2 25.00 15.00 10.00
[/tt]

Check the data type of your "Amount" field. If you are using Float (i.e. Double or Single) data types then you may be experiencing a round-off problem when comparing the sums. Try a HAVING clause like

HAVING Abs((sum(IIf([drcr]="D",amount,0))- sum(IIf([drcr]="C",amount,0))) >= 0.01;

 

[eerich]

My Amount field data type is 'Double' with 15 decimal places. I've tried to modify it but received a memory error before it could finish updating. The table has 2.3 million records. Is there a way I could add a rounding function to the 'Having' clause. I've tried:

Abs(round(sum(iif([drcr]="d",amount,0)))amount,2) - (round(sum(iif([drcr]="c",amount,0)))amount,2) <>0

but it didn't work. I just received a number of syntax errors, that i was unable to correct.

Any help is appreciated.

 

[Golom]

You've almost got it but you have a spurious "Amount" reference

Abs(round(sum(iif([drcr]="d",amount,0)),2) - (round(sum(iif([drcr]="c",amount,0)),2) <> 0

 

[eerich]

I can't seem to get beyond this error message:
Syntax error (comma)in query expression:

'ABS(round(sum(iif([DC]="S", glamount,0))),2) - (round(sum(IIF([DC]="H", glamount, 0))),2) <>0'.

My entire clause is:

Having ABS(round(sum(iif([DC]="S", glamount,0))),2) - (round(sum(IIF([DC]="H", glamount, 0))),2) <>0
Order by sum(glamount) desc
;

Any suggestions?