Results 1 to 5 of 5
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Run Sum query (e.g., credit card pay off query)

    Good morning:

    I'd like some assistance with generating a "Run Sum" query (based on principal balance, interest, and new charges).

    Attached are 2 files:
    1. Excel file with 2 examples
    2. Access containing 2 tables and 2 queries mimicking the process in Excel

    Example #1 (Excel):
    - Cell B4 lists the *starting* balance.
    - Cell range B5:B10 reference new monthly balance(s).
    - Cell range C4:C10 contain payments.
    - Cell range D4 : D10 calculate end-of-month (EOM) balance(s).

    Example #1 (in Access):


    - First of all, allow me to mention that I was able to derive at the same *final* EOM balance (i.e., $4,250) in record #7 (equivalent to cell D10 in XLS).
    - That said, however, the approach in Access is slightly different compared to XLS. Ultimately, there may be another *better* way of storing/calculating the values.
    - Finally, in the query I do NOT have the equivalent to XLS' column B (new monthly balance). Ideally though, I would like to also show the *starting* balance in my Access query.... and, btw, may contribute to the underlying issue in example #2.

    Now, let's transition to example #2. Allow me to first summarize the process based on the Excel scenario.

    Example #2 (Excel):
    - In principal, it is the same setup as example #1. However, this version includes 3 additional elements: "New Charges", "Interest", and "Interest Rate", where ...
    - D17 : D23 may include any new charges applied to the credit.
    - Cell H17 references the APR.
    - E17:E23 calculate the monthly interest charges which is the previous month's EOM * APR.
    - F17:F23 are derived based on a) previous month's EOM + b) any new charges + c) calculated interest.
    - Please note the difference in the interest calculation for the 1st and 2nd month. That is, in the 1st record, I don't have an EOM so the starting balance is used. In the 2nd record, however, interest is calculated based on the EOM.

    Now, here's where I need some help with... I attempted to mimic the XLS process in Access but I do NOT end up with the same EOM balance in record #7. That is, XLS's = $4,585.33 while ACCDB's = $4.423.83.

    Example #2 (in Access):
    - The calculated interest = $27.08 for the 1st record is identical in Excel and Access.
    - Once I transition to the 2nd record, the amounts start to slightly vary. That is, XLS's interest (rec #2) = $27.23 while the one in Access = $26.68.
    - I am confident the difference is due to the missing *equivalent* column B which has a different value (e.g., $5,027.08) as value vs. Access query calculating the interest based on the EOM_Balance.

    All that said, here's my question:
    1. How can I mimic the Excel approach in my Access query which would include the current starting balance based on last's month's EOM?
    2. At this time, I'm using a the "RUN SUM" as my EOM, but how can the calculated running sum be used as the new month's start balance?

    SQL for RUN SUM:
    Code:
    EOM_BALANCE: (Select Sum(T.PAYMENT) - Sum(T.NEW_CHARGES) + (Select ((Sum(T.PAYMENT) - Sum(T.NEW_CHARGES))*0.065/12) From tbl_Example_02 As T Where  T.ID<=tbl_Example_02.ID;) From tbl_Example_02 As T Where  T.ID<=tbl_Example_02.ID;)
    Btw, I'm completely open to a new/different process which may include a table for the principal starting balance as well a placeholder for the APR (vs. hard-coding it in my query).

    Thank you for any solutions that would mimic the XLS process in my Access query.

    Cheers,
    Tom
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    My advice is, start with redesigning your Excel table - something like columns
    EntryDate, EntryType, EntrySum, and optionally calculated columns like Month (e.g. in format "yyyymm")
    , where column EntryType contains values like "StartingBalance", "Deposit", "Payment" (use Data Validation list to determine them).
    Further you can add additional columns like InterestRate and Interest.
    and then add a report sheet, where
    a) you can select a month, and summary for this month is calculated (using formula(s) with SUMIFS() function mostly)
    or
    b) you can select a month, and summary for certain number o months (e.g. 12 months or less - depending on selected month) starting from selected month (again using SUMIFS() function mostly).

    The entry with EntryType "StartingBalance" must be entered with date in month before date for any other entry (e.g. when you start the table at January of year 2021, then this entry must be from year less than 2021).
    You enter EntriSum values eitehr as (+) or (-) values, or EntryType must determine the sign for those values.

    When you import your data into Access DB, simply link this Excel workbook into it, create an identical table in Access DB which will be updated from linked Excel table, and write a VBA code which:
    1. Reads data from linked table;
    2. Checks is the entry a new information or not;
    3. When information is new, adds it to Access table.
    (as it looks like you aren't updating older Excel entries, all this can probably be done using a single query)

    Further, with such table structure, quite simple Access queries can calculate all you asked for (and results can displayed/printed as report).

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you Arvil... 'appreciate the info.

    I had hoped to NOT use Excel for this process. Since posting the thread, I came across a method that allows me to bring up a field from a previous record.

    Code:
    BALANCE: Val(DLookUp("[EOM_BALANCE]","qry_Example_03","[ID]=" & [ID]-1))
    However, it's still not working in a single query given the EOM balance is dynamically calculated. If you have any other recommendations as to how this process can be completed *without* using Excel, I'd truly appreciate any additional recommendations/solutions. Thank you!

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Excel part was added because it looked like it is what you have now! This part is optional (but it will be much easier to convert old structure to new one in Excel, and read data into Access from there, instead trying to read data into Access from structure you described). Access is not the best tool to work with spreadsheets!

    Import all data into Access (as I described), or create a blank Access table and start from zero;
    Create a form for entering new entries into Access table;
    Create a summary query/queries which reads data from table and calculates wanted totals over wanted periods;
    Create report(s) based on query/queries.

    Some advice for Access calculations:
    To calculate the starting balance for month, summarize all entries earlier than 1st of this month (incoming entries (+), outgoing entries (-));
    To calculate end balance for month, summarize all entries earlier than 1st of next month.

    And an afterthought! Don't save any balances etc. into Access table! They must be calculated whenever there is a need for them!

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Arvil - yes, I don't want to read XLS into Access. I merely included the spreadsheets to illustrate the process.

    Simply want to develop a new process in Access that allows the calculation of the EOM. I am open a redesign in Access that mimics the process.

    Thanks for any ideas in advance.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-30-2019, 04:08 PM
  2. Replies: 2
    Last Post: 09-02-2017, 09:54 AM
  3. Replies: 1
    Last Post: 07-12-2016, 08:22 AM
  4. Replies: 2
    Last Post: 08-24-2011, 12:59 PM
  5. Credit Card Info, Where?
    By mastromb in forum Access
    Replies: 3
    Last Post: 05-26-2010, 12:37 AM

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