Diminishing Balance Calc in Query.
This is an offshoot of the earlier Function Running-Sum in Query Column. With a few changes to the earlier Function RunningSum(), we can easily calculate the loan balance-to-pay amount after deducting the monthly amount paid at each record level.
The loan amount is payable in monthly installments. Our simple task is to show the diminishing balance of the loan amount against each record-level installment amount in a separate Column of the Query. The last record will have the remaining balance amount for payment.
Let us pretend that the Loan Repayable Total Amount is 1000.
Sample Query Recordset.
The sample installment payment detail records are taken from the earlier Post: Running-Sum in Query Column, as given below.
The Query SQL that calls the DiminishingBal() Function.
The SELECT Query SQL that calls the DiminishingBal() Function, in a separate Column of the Query.
SELECT Table_Units.ID, Table_Units.Units, DiminishingBal([ID],"ID","Units","DiminishingQ1") AS DiminishingBalance FROM Table_Units;
The Query Recordset Image, with the result in the last column, is given below:
We are using the same Query recordset as the source for the earlier RunningSum() Function here for demonstration purposes. The Recordset should have a Unique value (Numeric or String) field and be used as the first parameter to the Function.
The Total Repayable Loan Amount is kept in a separate Table.
The Total Amount to be repaid to the Bank (1000) is kept in a separate Table with the following structure:
The DiminishingBal() Function VBA Code.
The VBA Code of the DiminishingBal() Function is given below:
Option Compare Database
Option Explicit
'Declare a Generic Object
Dim D As Object
Public Function DiminishingBal(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
'Function: DiminishingBal()
'Purpose : Calculate Diminishing Balance in a separate Column
'The Query can be used as source for other Processing needs,
'for Form View or Report
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date : December 2019
'Rights : All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
'Parameter List, in the Order of it's placement
'1. Key Value Data Field
'2. Key-Field Name in String Format
'3. Field-Name for Calcuating Running Sum in String Format
'4 Query-Name in String Format
'-----------------------------------------------------------
'Remarks: The Key-Value Field should have Unique Numeric or
'String Values.
'-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y As Long
Dim p As Variant
On Error GoTo DiminishingBal_Err
y = DCount("*", QryName)
'If the Function is not called by the same Query
'then initialize Dictionary Object and Variables
If SumFldName <> fld Or K > y Then
fld = SumFldName
Set D = Nothing
K = 0
X = 0
End If
K = K + 1
If K = 1 Then 'The major process of the function starts here
Dim DB As Database, rst As Recordset
'Create and instantiate the Dictionary Object
Set D = CreateObject("Scripting.Dictionary")
'Get Loan Repayable Amount
X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1")
'Open the EMI Recordset
Set DB = CurrentDb
Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
'Calculate cumulative record-level summary and
'add the value into Dictionary Object as it's Item
While Not rst.EOF And Not rst.BOF
'read the record summary field value and add it to total
X = X - rst.Fields(SumFldName).Value
'read current record key field value
p = rst.Fields(KeyFldName).Value
'add the total value to dictionay object
'as Key, Item pair
D.Add p, X
' repeat this process for all records
rst.MoveNext
Wend
'close recordset and remove the database objects
rst.Close
Set rst = Nothing
Set DB = Nothing
'Retrieve the first item from Dictionary,
'using the first Key passed as parameter,
'and return to the function calling record in the Query
DiminishingBal = D(IKey)
Else
'Subsequent calls with the record Key passed as parameter
'will retrieve other record values from Dictionary and
'returns to their corresponding records in the Query.
DiminishingBal = D(IKey)
End If
'A control forcing to initialize the static variables
'when the program is rerun for the same query.
If K = y Then
K = K + 1
End If
DiminishingBal_Exit:
Exit Function
DiminishingBal_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "DiminishingBal()"
Resume DiminishingBal_Exit
End Function
How the Function Works.
In the Global declaration area of the VBA Module, Variable D is declared as an Object.
The DiminishingBal() Function needs four parameters:
- A Unique Value Field (Numeric or String Values) is the first parameter. The parameter is declared as a Variant data Type.
- The Unique Value’s Field Name is the second parameter in String format.
- The Loan Installment Value Field Name.
- The Query Name is the fourth Parameter.
- If the control variable K value is more than the Query record count variable y, then the Static variables are reset to their initial values and the Dictionary Object is deleted from memory.
- Or, if the installment value Field Name is different from the field name saved in Variable fld during the last call of the function, then it assumes that the Function is called from a different Query Column and resets the Static Variable Values. The Dictionary object is deleted from memory.
Four Static Variables K, X, fld, and y are declared. They must be Static Variables to retain their values between repeated calls of the Function, from each record of the Query. The Variable p is declared as a Variant Type normal variable to hold the Key-Value (either Numeric or String) of each record.
The DCount() Function takes the Query record count in Variable y. The Value in this Variable is used as a control to check when to reset the Static Variable Values to their initial Values and to remove the Dictionary Object from memory. This control is necessary if the same Query is run more than once, consecutively.
Next, Variable K is incremented by 1. When K=1, the main action of the Function starts. The Database and Recordset Objects are declared in the DB and the rst variables, respectively.
In the next executable statement, Set D = CreateObject("Scripting.Dictionary") creates the Dictionary Object with the CreateObject() method and assigns it to the Object variable D, which was declared in the Global Area of the Module.
There are other ways to declare and use this Object by adding the Microsoft Scripting Runtime File to the Microsoft Access Reference Library List. After that, you can create an instance of the Dictionary Object in the following way:
Dim D As Dictionary Set D = New Dictionary
If you are new to the Dictionary Object and its usage, visit the Post: Dictionary Object Basics. There are five Posts on this Topic, and you can find the links at the end of this Page.
Next, we need the value of the repayable total Loan Amount and retrieve it from its Table tblRepay with the dlookup() function in the statement: X = DLookup("[LoanAmt]", "tblRepay", "[id] = 1"). There is only one record in the Table with ID 1, so omit the criteria part.
The Query Recordset is open to read records one by one. The first record’s amount paid to the bank is deducted from the Loan Amount (1000) in Variable X. The Unique Key value of the record is retrieved and saved in Variable p in the next statement.
The balance loan amount, calculated after deducting the repaid Amount, is added to the Dictionary Object with the Dictionary Object’s Add Method as its Item Value, with the Unique Key field value in the variable p as Dictionary-Key in the statement: D.Add p, X. The Dictionary’s Add method always adds its Item value in Key, Item pairs.
Note: If the Key-Value is not unique, the Add method fails with Errors.
With the rst.MoveNext statement moves to the next record and adds the result value to the Dictionary Object.
This way, the individual record value is deducted from the remaining loan balance amount at that level and added to the Dictionary Object as its Item.
Note: Here, you may ask why the Dictionary Object is chosen to hold all the calculated values rather than in an Array. Yes, it can be done, but that method needs more statements to store and retrieve the values in a two-dimensional array. It will become more complicated when the Query Record’s Unique Key Value is in String form. The Dictionary Object allows you to retrieve the value in either sequential or random order based on its Key. Here, the Random method works fine with the Key-Value Type in the Numeric or String form.
When all the record processing is complete, the record set and Database Objects are closed.
What you have to keep in mind at this point is that the value in variable K is still 1, and the first Parameter IKey retains the unique ID value. At the first record-level function call, DiminishingBal() calculated all record-level balance loan amounts and added them as Dictionary Object Items. The function parameter IKey still holds the first record’s Unique ID value. That is why we have used a separate variable p for the individual record key during the processing of all records.
So, the entire record-level processing is done during the first call of the function, initiated from the first Query record, and all record-level result values are held in temporary storage in the Dictionary Object.
The next statement DiminishingBal = D(IKey) retrieves the first value, adds it to the Dictionary using the unique parameter value IKey, and returns the value to the calling first record of the Query.
The next call from the second record of the Query increments the variable K by 1 (now K=2), and the program takes the ELSE path of the IF...Then the statement retrieves the second Item value from the Dictionary Object, using the IKey parameter, and returns it to the corresponding Query record.
The rest of the DiminishingBal() function call, from the remaining records, will route the program control only through the ELSE path because the value in Variable K is greater than one, retrieves the values from the Dictionary Item, and returns it to the calling record.
The Next If . . . Then statement checks whether the value in variable K = y. Variable y holds the total record count of the Query. If it is found to be True, then it assumes that the last call of the DiminishingBal() function has arrived. At this point, the K variable is made greater than the control variable y.
This is necessary to initialize the Static Variables during the Query rerun. If any change is made to the Source Data before the rerun, it will not be reflected in the balance amount calculated earlier, because it will keep taking the ELSE route in the If ... Then statement and retrieve the old value from the Dictionary Object.
The Demo Database, with all the necessary Objects and the Code Module, is attached for your convenience to download and try it out straight away.
Download DiminishingBalance.zip
WrapperClass.zip
