Thread: Access 2010: opening & running balance problem

1. Access 2010: opening & running balance problem

I have an Access 2010 Report derived from an Account Table for a Trading Account with the following fields:
Transaction_ID (Primary Key)
Transaction_Date
Transaction_Description
Transaction_Amount
Brokers_Name
Typical data in the Report looks like this:

Opening Balance \$10,000.00
Row No____ Date________Description_________Amount______Runnin g Balance
1_________ 17 Feb 09 ___ Fee ______________ -\$10.00 _____ \$9,990.00
2_________ 19 Feb 09 ___ Fee ______________ -\$10.00 _____ \$9,870.00
3_________ 19 Feb 09 ___ Fee ______________ -\$10.00 _____ \$9,870.00
4_________ 19 Feb 09 ___ Buy BHP __________ -\$100.00 _____ \$9,870.00
5_________ 25Feb 09 ___ Sell BHP ___________ \$200.00 ____ \$10,070.00
6_________ 25 Feb 09 ___ Fee ______________ -\$10.00 ____ \$10,070.00
7_________ 25 Feb 09 ___ Fee ______________ -\$10.00 ____ \$10,070.00

The running Balance is calculated using the following folmula in the Running Balance TextBox:

=[Tbx_Opening_Balance]+ DSum("[Transaction_Amount]","Tbl Trading Account","[Transaction_Date] BETWEEN #" & [Forms]![Fm Account Statement Parameters].[Tbx_Period_Start] & "# " & "AND " & "#" & [Transaction_Date] & "# " & "AND [Broker_Name] = '" & [Forms]![Fm Account Statement Parameters].[Tbx_Broker_Name] & "' " & "AND NOT [Transaction_Type] = 'Paper Trade' ")

The formula works to produce a nice running balance PROVIDED THAT there are not duplicated dates. As you can see from my above Report segment, duplicated dates are the norm in my Table as each time there is a trading transaction there are fees associated with it that occur on the same day. Unfortunately the above formula shows aggregates all amounts record on a given date and shows that total against each entry, rather than provide a correct running balance. The correct running balance would be:

1 ___ \$9,990.00
2 ___ \$9,980.00
3 ___ \$9,970.00
4 ___ \$9,890.00
5 __ \$10,090.00
6 __ \$10,080.00
7 __ \$10,070.00

What changes must I make to my formula to correct this erroneous result?

Any help will be very much appreciated as I've spent too many hours trying to beat this problem.

2. If your primary key is AutoNumber you could add a condition that the PK is <= current record PK.

3. RG is, of course, right, but I am not too sure that it is an easy thing to implement on the report itself.
I usually handle SQL way better than Access's native stuff (for lack of practise) , so if I had to do this, I would most likely do it in the query the report is based, calculating the running balance in the query itself.

4. Hi Retired Geek and Ruirib, thanks for your prompt replies .

The autonumber Primary Key cannot be used as some entries ( eg interest and fees) are for a date are earlier than the latest entries, thus the Primary Key may be greater than the Date would suggest.

Ruirib, I like your idea of using a Query to calculate the running balance, how do I do that?

Thanks again guys.

5. Ok, I don't mind trying that, but your reply to RG's suggestion raises one issue - it cannot be assumed that those transactions, in your example, in rows 2, 3 and 4, have ascending IDs? If it can't, what other way would you suggest could be used to determine which transactions should be used to calculate the running balance for a specific row? Or, if you prefer, relative to the calculation for row 2, what condition should be used to avoid including the amounts in rows 3 and 4?

6. I agree with ruirib. To do what you want you need to sort the transactions into the order they happened. You would normally do that either with the autonumber, or with a combination of date and time.
If the autonumbers are out of order, and you don't have a time field I don't think you can do what you want.
Would it be enough to sort by Date as the primary sort, then by autonumber within Date?

7. If my previous suggestion is OK then this expression should work in the report.

=[Tbx_Opening_Balance]+ DSum("[Transaction_Amount]","Tbl Trading Account","(([Transaction_Date] > #" & [Forms]![Fm Account Statement Parameters].[Tbx_Period_Start] & "# AND < #" & [Transaction_Date] & "#) or ( ([Transaction_Date] = #" & [Transaction_Date] & "#) and ([TransactionID] < " & [Transaction_ID] &" ))) AND [Broker_Name] = '" & [Forms]![Fm Account Statement Parameters].[Tbx_Broker_Name] & "' " & "AND NOT [Transaction_Type] = 'Paper Trade' ")

I don't have any way of testing this, so apologies if there are typos /syntax errors.

It is trying to sum the transactions where the Transaction Date is less than the current one, plus those where the Date is the same and the Tranaction_ID is less. For this to look right, the sort order should follow my previous suggestion.

8. Hi John,

Many thanks for your suggestions which were instrumental in me solving my problem.

The formula I finally used in my report textbox is:

=[Tbx_Opening_Balance]+DSum("[Transaction_Amount]","Tbl Trading Account","(([Transaction_Date] >= #" & [Forms]![Fm Account Statement Parameters].[Tbx_Period_Start] & "# AND [Transaction_Date] <= #" & [Transaction_Date] & "#) AND [Transaction_ID] <= " & [Transaction_ID] & " ) AND [Broker_Name] = '" & [Forms]![Fm Account Statement Parameters].[Tbx_Broker_Name] & "' " & "AND NOT [Transaction_Type] = 'Paper Trade' ")

For this formula to work correctly it is ESSENTIAL that the “Transaction_ID” (the Primary Key in “Tbl Trading Account” and “Transaction_Date” are ascending in synch. To make this clear for others reading this thread, the rows in “Tbl Trading Account” should look like this:

[Transaction_ID] [Transaction_Date]

201 ____________ 17-Feb-2000
202 ____________ 17-Feb-2000
203 ____________ 18-Feb-2000
204 ____________ 3-Mar-2000
205 ____________ 15-Mar-2000

The formula WILL NOT work correctly if the the rows in “Tbl Trading Account” look like this:

[Transaction_ID] [Transaction_Date]

201 ____________ 17-Feb-2000
150 ____________ 17-Feb-2000 <<=== note Transaction_ID not ascending
203 ____________ 18-Feb-2000
99 ____________ 3-Mar-2000 <<=== note Transaction_ID not ascending
205 ____________ 15-Mar-2000

My original “Tbl Trading Account” had rows like this and caused all sorts of confusing issues. I resolved synchronisation of the “Transaction_ID” and “Transaction_Date” by following the instructions contained in Microsoft’s article “How to reset an AutoNumber field value in Access, Article ID: 812718 - Last Review: August 17, 2007 - Revision: 7.3” which can be found at http://support.microsoft.com/kb/812718. I’ve extracted the Access 2007 instructions below and added a few more instructions, comments and examples to make them clearer (I hope):

In Access 2007/2010, follow these steps:
1. Make a copy of the main table, in my case “Tbl Trading Account” as “Tbl Trading Account ORIG” and keep this in case of problems or screw ups
2. Make note of the AutoNumber field name in the main table, in my case the “Transaction_ID” field in “Tbl Trading Account”.
3. Delete the AutoNumber field, ie “Transaction-ID”, from the main table, ie “Tbl Trading Account”.
4. Copy the structure of the main table, ie “Tbl Trading Account” and then create a new table, say “Tbl Trading Account NEW”.
5. Click the Create tab, and then click Query Design in the Other group.
6. In the Show Table dialog box, select the main table, ie “Tbl Trading Account”. Click Add, and then click Close.
7. To select the fields from the main table, ie “Tbl Trading Account”, double-click the required fields (probably all of them). Do this for all the fields except for the AutoNumber field (which you can’t because you’ve already deleted it) in the Table view of the main table, ie “Tbl Trading Account”.
8. In the query design table, set the Sort for “Transaction_Date” field to “Ascending” (NOTE: This is a critical step to ensure that the ‘Transaction_ID” field autonumber is ascending along with the ascending dates is the “Transaction_Date” field.)
9. On the Design tab, click Append in the Query Type group. This changes the query type.
10. From the Table Name list, select the new table that you created in step 2, ie “Tbl Trading Account NEW” and then click OK.
11. On the Design tab, click Run in the Results group.
12. The following message appears:
You are about to append # row(s) into a new table.
Click Yes to insert the rows.
1. Close the query.
2. Right-click the new table, ie “Tbl Trading Account NEW” and then click Design View.
3. In the Design view for the new table, ie “Tbl Trading Account NEW”, add a new AutoNumber field that has the same field name that you deleted in step 1, ie “Transaction_ID”. Set this AutoNumber field as the Primary Key to the new table, ie “Tbl Trading Account”, and then save the table.
4. Close the Design view window.
5. Rename the main table name, ie “Tbl Trading Account” to say “Tbl Trading Account TEMP”. Rename the new table name, ie “Tbl Trading Account NEW” to the main table name, ie “Tbl Trading Account”.
Contented reporting from now on…

BygAuldByrd

9. Glad you worked it out.

I thought my suggested solution would work with the TransactionIds not being in Date order.

Isn't the problem that Transactions are entered into the system after the event and so you will keep getting them out of order again in the future?

10. Hi John,

Your formula produced an "#error" response and I can't find any typos. Haven't been able to figure out why.

I've since found an issue with my formula described earlier where by if I enter a transaction that predates other transactions in the report the running balance gets screwed up:

Transaction_ID Transaction_Date Amount Running Balance

468 __________ 01-Jun-2011 ___ -\$300.00 ____ \$31,304.65
469 __________ 01-Jun-2011 ____ -\$11.00 ____ \$31,293.65
508 __________ 01-Jun-2011 ___ \$1000.00 ____ \$32,293.65 <<<= OK
509 __________ 01-Jun-2011 ___ \$5000.00 ____ \$37,293.65 <<<= OK
470 __________ 02-Jun-2011 ______ -0.70 ____ \$31,292.95 <<<== Oops?? forgot to add to the previous balance
471 __________ 02-Jun-2011 _____ \$45.00 ____ \$31,247.95

Got any more suggestions?

Cheers

BygAuldByrd

11. My only suggestion is get the expression I gave fixed. But finding errors in these expressions can be tricky.

I have found a few already, but there still maybe something wrong. I have got it working now. See below.

=[Tbx_Opening_Balance]+ DSum("[Transaction_Amount]","Tbl Trading Account","( (([Transaction_Date] >= #" & format([Forms]![Fm Account Statement Parameters].[Tbx_Period_Start],"mm/dd/yyyy") & "#) AND ([Transaction_Date]< #" & format([Transaction_Date],"mm/dd/yyyy") & "#)) or ( ([Transaction_Date] = #" & format([Transaction_Date],"mm/dd/yyyy") & "#) and ([Transaction_ID] <= " & [Transaction_ID] &" ))) AND ([Broker_Name] = '" & [Forms]![Fm Account Statement Parameters].[Tbx_Broker_Name] & "') AND NOT ([Transaction_Type] = 'Paper Trade' )")

• In Australia we need to wrap dates in format([datefield],"mm/dd/yyyy") to convert to US date format for correct comparisons. Without this you don't get any errors, just the wrong answers.
• When I changed your Between...And to a >= and < I forgot to repeat the field name.
• Underscore missing from Transaction_ID (I never use underscores and spaces, so I never have to remember to put them in.)

My strategy to try to fix them is to remove some conditions and get a simpler version working (gives a number), then progressively add more in.
runningbalance.gif

12. Hi Again John,

Ah success at last!!!!

That format business is a real trip up. Now your formula works just fine with IDs later than should be for the dates and for earlier IDs for later dates.

Many thanks again for your assistance - it has been most helpful.

Regards

BygAuldByrd
(pseudonym for a not so small older codger named Bird)