Results 1 to 14 of 14
  1. #1
    toni2oni is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    5

    Calculating a field where its value depends on the same field of the previous row

    I'm trying to get some help with how to write a query. I have a table that contains the Year and a value EC. Using that table, I am trying to produce the table below.


    Year BBF EC Int Total
    2013 - 25 1.00 26.00
    2014 26.00 85.96 3.44 115.40
    2015 115.40 100.05 4.00 219.45
    2016 219.45 205.96 8.24 433.65
    Total from
    previous year
    4%(BBF+EC) BBF+EC+Int

    I'm not sure how to write the sql query as the total is dependent on the BBF, but the BBF fields also needs the total from the previous year.

    Any help would be appreciated.

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review http://allenbrowne.com/subquery-01.html#AnotherRecord

    Or build a report and use textbox RunningSum property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    toni2oni is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    5
    In the example on the link, the subquery selects a value that already exists in the table. In my scenario, the previous value that I need [Total] doesn't exist in the table, it is calculated using BBF which itself depends of the total from the previous row.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You're right, after taking a closer look, a query is not practical.

    I tested a report use RunningSum property and it produces the exact output as your example.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    toni2oni is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    5
    How to use RunningSum in the report?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Name texboxes like tbxEC, tbxInt and bind them to respective fields. Another textbox named tbxTotal with expression in ControlSource: =[EC]+[Int]. Set RunningSum property to OverAll.

    Another textbox named tbxBBF with expression in ControlSource: =tbxTotal-tbxEC-tbxInt
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    toni2oni is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    5
    The thing is, INT is also a calculated field based on the BBF field.
    Int = 4%(BBF+EC).

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Really!! Are you trying to drive me insane? So Int is not data in table? Okay, I should have paid more attention to formulas in the last row of your table.

    Some new textbox names.

    OverAll RunningSum in tbxSumEC: =[EC]

    OverAll RunningSum in tbxSumInt: =[tbxEC]*0.04

    Not RunningSum in tbxTotal: =[tbxSumEC]+[tbxSumInt]

    Not RunningSum in tbxInt: =[tbxEC]*0.04

    Not RunningSum in tbxBBF: =[tbxTotal]-[tbxEC]-[tbxInt]

    You can even hide the running sum textboxes.
    Last edited by June7; 10-13-2017 at 06:41 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    FYI........

    "INT" is an extremely poor choice for an object name. It is a (JET) reserved word AND a built in function.




    [BeginRant]

    You are using Calculated fields in tables?? Tsk, tsk. The calculation should be in query.

    [/EndRant]

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think by 'produce the table' OP meant a query datasheet. Query is mentioned in first and last sentences of OP

    And Year is also a reserved word - it is a function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    toni2oni is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    5
    Quote Originally Posted by June7 View Post
    I think by 'produce the table' OP meant a query datasheet. Query is mentioned in first and last sentences of OP

    And Year is also a reserved word - it is a function.
    Correct!

    Thanks for your help for far June7. I'm still not getting the figures that I want. I realized that I, in fact, made an error in the table above but the formula in the last row is what it should be. The table values should look like this.


    Year BBF EC Int Total
    2013 - 25 1.00 26.00
    2014 26.00 85.96 4.48 116.44
    2015 116.44 100.05 8.66 225.15
    2016 225.15 205.96 17.24 448.85
    Total from
    previous year
    4%(BBF+EC) BBF+EC+Int

    I might be trying to do the impossible.

    OverAll RunningSum in tbxSumInt should really be: =([tbxEC]+[tbxBBF])*0.04 but this throws an error

    If there's no other way I'll simply export the data to excel and format afterwards.

    Thanks

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    It is not impossible in MS Access, but it is impossible for MS Access query. To get the wanted result you need to write a recursive query, which isn't supported in Access.

    To do this in Access, you have to write a procedure, which:
    1. Creates an empty recordset with structure you need to be returned;
    2. Determines the 1st and last year in your table and saves values to variables;
    3. Calculates values for 1st year (you can define a querystring in VBA and run it) and writes the result as row into recordset;
    4. Saves Total into variable;
    5. The Start of cycle! Saves next year (1st year + 1) into variable;
    6. If saved year is bigger than last year, then ends the procedure, otherwise continues;
    7. Tests for existence of next year. If such exists, then continues, otherwise starts new cycle (I hope this will not happen, otherwise you have to decide how proceed with your calculations!);
    8. Calculates values for active year (you can use the same querystring as at start, but you assign saved Total value as BBF) and writes the result as row into recordset;
    9. Saves new Total into variable and returns to 5. The end of cycle!;
    10. Returns the recordset.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yep, officially insane now. Options appear to be Arvil's suggestion or export to Excel.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by June7 View Post
    Options appear to be Arvil's suggestion or export to Excel.
    Or to port DB to MS SQL Server, where it is possible to create a recursive query. But however you describe a recursive query, you don't use a word "simple" there

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-07-2016, 09:28 PM
  2. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  3. Replies: 1
    Last Post: 04-29-2016, 04:03 AM
  4. Replies: 3
    Last Post: 09-19-2014, 08:22 AM
  5. Replies: 3
    Last Post: 05-30-2014, 12:34 AM

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
  •  
Other Forums: Microsoft Office Forums