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.
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.