I think it might be possible to get close to what you want provided you
return the invoice dates and payment dates in a single column:
SELECT CustomerID, [Date], InvoiceNo, ProductNo,
Qty, Amount, NULL AS PaidAmount,
(SELECT SUM(Amount)
FROM tblInvoice As 12
WHERE I2.CustomerID = I1.CustomerID
AND I2.[Date] <= I1.[Date])
-(SELECT SUM(AmountPaid)
FROM tblPayments
WHERE tblPayments.CustomerID = I1.CustomerID
AND tblPayments.DatePaid < I1.[Date])
AS Balance, 0 AS SortColumn
FROM tblInvoice AS I1
UNION ALL
SELECT CustomerID, DatePaid, NULL, NULL, NULL,
NULL, PaidAmount,
(SELECT SUM(Amount)
FROM tblInvoice
WHERE tblInvoice.CustomerID = P1.CustomerID
AND tblInvoice.[Date] <= P1.DatePaid)
-(SELECT SUM(AmountPaid)
FROM tblPayments AS P2
WHERE P2.CustomerID = P1.CustomerID
AND P2.DatePaid <= P1.DatePaid), 1
FROM tblPayments AS P1
ORDER BY CustomerID, [Date], SortColumn;
The UNION ALL operation tacks the results of the two constituent queries
together, with Nulls being inserted at the column positions in each which are
only represented by columns in the other's table. The two subqueries in the
first part sum the invoice amounts for the current customer less the sum of
the payment amounts for the cutomer before the current date. The two
subqueries in the second part sum the invoice amounts for the current
customer less the sum of the payment amounts for the customer up to the
current date. These should give the balances for the invoice and payment rows
respectively. The SortColumn, by means of the constants 0 and 1, should
ensure that any payment by a customer on the same day as an invoice for that
customer comes after the invoice in the result table.
Ken Sheridan
Stafford, England