How to calculate the opening balance

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.

What I would need to see is the SQL for your query. 

To calculate a balance as of any point in time, you would have a query that adds the credits and subtracts the debits. I don't recall how you have things setup, but that is what you need. If you have a query with a running total, it's similar to that. 

SELECT Account, Sum(Credits) As Credits, Sum(Debits) as Debits, Sum(Credits) - Sum(Debits) AS Balance

GROUP BY Account

WHERE TransactionDate < #10/1/2016#;

That should give you the balance as of 9/30/2016 for each account.

Sorry I'm not now understanding what you are saying , example my report is based on a query and a parameter form is attached which controls the following:

(1) The customer selection

(2) Period Selection

Kindly try to demostrate what I should do on this.

Regards

Chris

Well the analysis is ok but I'm not convinced on the following:

WHERE TransactionDate < #10/1/2016#;

That should give you the balance as of 9/30/2016 for each account

So suppose I want to calculate the opening balance for for January 2016, it means that again I have to reset the parameter manually for example 

WHERE TransactionDate < #1/1/2016#;

The above should give me the balance as at 12/30/2015

Can't it be done by attaching a parameter form so that users do not have to continue reseting the dates in the query?

Regards

Chris

You need to filter your query for transactions prior to Oct 1.  Your balance query should add credits and subtract debits prior to Oct 1 to get the opening balance.