Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Question 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.
    Last edited by BygAuldByrd; 2011-08-10 at 10:14. Reason: Tables not being displayed

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 766 Times in 700 Posts
    If your primary key is AutoNumber you could add a condition that the PK is <= current record PK.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,207
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    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.

  5. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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.

  6. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,207
    Thanks
    129
    Thanked 1,145 Times in 1,054 Posts
    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?

  7. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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?
    Regards
    John



  8. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  9. #8
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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

  10. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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?
    Regards
    John



  11. #10
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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

  12. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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
    Regards
    John



  13. #12
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    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)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •