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:
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).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;)
Thank you for any solutions that would mimic the XLS process in my Access query.
Cheers,
Tom