Cfrgtkky

I've created a statement report that pulls from a query of a couple tables and groups them by ClientID and a couple sorts and each client has their statement on a separate page with their total owed.

The issue is that it shows clients that currently owe nothing and I only wish to print statements where their summed ChargeAmt is greater than zero. I cannot seem to be able to use Totals with a sum on the ChargeAmt since it just shows me the sum. Even when adding ChargeAmt again with the GroupBy option, it will continue to only show the summed value.

SELECT ChargeAccount.ClientID, Sale.SaleNo, Sale.SaleDate, Sale.LastName, ChargeAccount.ChargeAmt, Sale.FirstName, Sale.MI, Sale.StreetAddress, Sale.City, Sale.State, Sale.ZipCode, Sale.SaleTotal, ChargeAccount.ChDate

FROM ChargeAccount INNER JOIN

     Sale

     ON ChargeAccount.SaleNo = Sale.SaleNo

WHERE (((ChargeAccount.ChargeAmt)<>0))

ORDER BY Sale.LastName, ChargeAccount.ChargeAmt DESC; 

Can I add a SUM function, maybe within a HAVING clause or something? I'm very new to this so I apologize if this is a simple matter.

I would suggest joining your query to an aggregated subquery which returns the ClientID for those accounts for which the SUM of ChargeAmt is non-zero.

This removes the need for your WHERE clause as the selection criteria is implicit in the INNER JOIN:

SELECT 

    ChargeAccount.ClientID, 

    Sale.SaleNo, 

    Sale.SaleDate, 

    Sale.LastName, 

    ChargeAccount.ChargeAmt, 

    Sale.FirstName, 

    Sale.MI, 

    Sale.StreetAddress, 

    Sale.City,

    Sale.State, 

    Sale.ZipCode, 

    Sale.SaleTotal, 

    ChargeAccount.ChDate

FROM 

    (ChargeAccount INNER JOIN Sale ON ChargeAccount.SaleNo = Sale.SaleNo) 

    INNER JOIN

    (

        SELECT t.ClientID

        FROM ChargeAccount t

        GROUP BY t.ClientID

        HAVING SUM(t.ChargeAmt) <> 0

    ) s ON ChargeAccount.ClientID = s.ClientID

ORDER BY 

    Sale.LastName, ChargeAccount.ChargeAmt DESC;