I'm totaling up amounts owed and subtracting amounts paid and only want to show a record when it still owes money. I'm very close but I can't figure out how to use the alias from the SELECT in the WHERE clause
Code:
SELECT tblReceivables.Customer, Sum(tblReceivables.InvTotal) AS totalowed, Sum(tblCustomerPayments.amount) AS totalpaid, (totalowed-totalpaid) AS balance
FROM tblReceivables LEFT JOIN tblCustomerPayments ON tblReceivables.InvoiceNumber = tblCustomerPayments.fkInvoiceID
GROUP BY tblReceivables.Customer;
I've tried just WHERE balance > 0 from both the SQL editor and the builder but when run it asks me for balance. Also tried totalowed - totalpaid but it just asks for both.
I know it'll be simple but I just can't seem to figure it out.
thanks!