How to calculate the opening balance

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

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. 

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.

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

Who said anything about manually resetting the criteria? You could have a query that always show the previous month's balance:

<DateSerial(Year(Date()),Month(Date()),1)

Or you could have a query that calculates the month end balance as of any selected month by having the user select a date or a month and  year on a form and use:

<Forms!formname!MonthBegin

Ok thanks alot I will try it

Regards

Chris

In a tradition design of a general ledger system, the balance is closed off every month and it is brought forward as the opening balance of next month. You don't rely on a query to determine the closing balance.

Really?? Then how does one calculate the balance in the first place?While some systems may store the closing value to "close" the books for the month. The balance is still calculated using a query. Storing the closing balance or having it "brought forward as the opening balance" is mostly to prevent having to go back to the beginning of time to calculate the balance. So one starts with the stored opening balance. But this does depend on performance. 

...........it means that again I have to reset the parameter manually

I gave you examples using date literals.  In real life the dates would of course either be parameters, normally references to a control in a form, or, if the report is always to return data for the current month you can compute the first of the month with an expression as Scott describes.