balance

[simon davies]

Access 2k

How can I show an opening balance on a monthly report (
similar to a bank statement ).
It's for customer statements of invoices raised and
payments recieved, the opening balance being last month's
outstanding balance.

Thanks in advance

Simon

 

[Steve Schapel]

Simon,

To simplify, I would do it in 3 steps:
1. Make a query based on your Invoices table to calculate the total
invoices issued to each customer. The SQL of this query might look
something like...
SELECT CustomerID, Sum([InvoiceAmount]) AS OpeningAmount FROM Invoices
GROUP BY CustomerID HAVING InvoiceDate <= Date()-Day(Date())
2. Make a query based on your Payments table to calculate the total
payments received from each customer. The SQL of this query might
look something like...
SELECT CustomerID, Sum([AmountPaid]) AS OpeningPaid FROM
PaymentsReceived GROUP BY CustomerID HAVING PaymentDate <=
Date()-Day(Date())
3. Add both these queries to the existing query that your Statement
report is based on, with Left Joins to both on the CustomerID field,
and then use a calculated field in the query like this...
OpeningBalance: Nz([OpeningAmount],0)-Nz([OpeningPaid],0)

(the above not tested!)

- Steve Schapel, Microsoft Access MVP