Take look at Balances.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.
This little demo file includes a number of queries for computing running balances, including one where the transactions are returned per customer using a table with separate credit and debit columns. If this query is restricted by CustomerID and TransactionDate
as follows:
SELECT T1.CustomerID, T1.TransactionDate, T1.Credit, T1.Debit,
SUM(T2.Credit-T2.Debit) AS Balance
FROM TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
ON (T2.TransactionID<=T1.TransactionID Or T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID)
WHERE T1.CustomerID = 1 AND T1.TransactionDate BETWEEN #2009-11-01# AND #2009-11-30#
GROUP BY T1.CustomerID, T1.TransactionDate, T1.TransactionID, T1.Credit, T1.Debit
ORDER BY T1.CustomerID, T1.TransactionDate DESC , T1.TransactionID DESC;
It returns the following result table, though the data might differ in the online file; I'm not sure how up to date it is with my local version:
CustomerID TransactionDate Credit Debit Balance
1 01/11/2009 £4.00 £0.00 £0.00
1 01/11/2009 £0.00 £1.00 -£4.00
1 01/11/2009 £2.00 £0.00 -£3.00
If the restriction by date is removed it returns the following result table:
CustomerID TransactionDate Credit Debit Balance
1 01/11/2009 £4.00 £0.00 £0.00
1 01/11/2009 £0.00 £1.00 -£4.00
1 01/11/2009 £2.00 £0.00 -£3.00
1 05/04/2009 £0.00 £20.00 -£5.00
1 06/01/2009 £5.00 £0.00 £15.00
1 05/01/2009 £10.00 £0.00 £10.00
As you can see the first post-transaction balance, taking account of transactions prior to 1st November 2009, has been preserved in the first query when the transactions are restricted to November 2009.
If we now add a correlated subquery to return the opening balance:
SELECT T1.CustomerID, T1.TransactionDate, T1.Credit, T1.Debit,
SUM(T2.Credit-T2.Debit) AS Balance,
(SELECT SUM(T3.Credit-T3.Debit)
FROM TransactionsCD AS T3
WHERE T3.CustomerID = T1.CustomerID
AND T3.TransactionDate < #2009-11-01#) AS OpeningBalance
FROM (TransactionsCD AS T1 INNER JOIN TransactionsCD AS T2
ON (T2.TransactionID<=T1.TransactionID Or T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID))
WHERE T1.CustomerID = 1 AND T1.TransactionDate BETWEEN #2009-11-01# AND #2009-11-30#
GROUP BY T1.CustomerID, T1.TransactionDate, T1.TransactionID, T1.Credit, T1.Debit
ORDER BY T1.CustomerID, T1.TransactionDate DESC , T1.TransactionID DESC;
The result table is:
CustomerID TransactionDate Credit Debit Balance OpeningBalance
1 01/11/2009 £4.00 £0.00 £0.00 -£5.00
1 01/11/2009 £0.00 £1.00 -£4.00 -£5.00
1 01/11/2009 £2.00 £0.00 -£3.00 -£5.00
The fact that the opening balance is repeated in each row is not important as in a report based on this query the control bound to this column would appear only at the start of the report in the report header.
PS: As my queries follow the convention of returning the transactions in descending date order the opening balance would in my case be more likely to go in the report footer rather than header. Your order may differ.