Diminishing Balance Calc in Query ~ LEARN MS-ACCESS TIPS AND TRICKS

Wednesday, December 18, 2019

Diminishing Balance Calc in Query

Introduction.

This is an offshoot of the earlier Function Running-Sum, in Query Column. With few changes in the earlier Function RunningSum() we can easily calculate and find the loan balance-to-pay amount, after deducting the monthly paid amount, 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 payment amount.

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 record set used as a source for the earlier RunningSum() Function and used here also 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 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.

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 record count of the Query 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.

If the value in the control-variable K is more than the Query record count in variable y then resets the Static variables 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.

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 repayable total Loan Amount and retrieves it from it’s 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 number 1 and you may 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 from the record 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 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 then the Add method fails and will end up with Errors.

With the rst.MoveNext statement takes the next record for the same sequence of processing and adds the result value to the Dictionary Object.

This way individual record value is deducted from the remaining balance loan 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 the value retrieved 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 still the value in variable K=1 and the first Parameter IKey retains the first records Unique Id Value. At the first record level call of the function DiminishingBal() itself, we have calculated all the record level balance loan amount values, one by one, and added the result to the Dictionary Object as its Items. The function parameter IKey still holds the first record’s Unique ID value. That is the reason why we have used a separate variable p for individual record key values while processing all the records.

So, the entire record level processing is done during the first call of the function, initiated from the first record of the Query, and all the record level result values are held in temporary storage in the Dictionary Object.

The next statement DiminishingBal = D(IKey) retrieves the first value and added 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 to the ELSE path of the IF. . .Then statement, retrieves the second Item value from the Dictionary Object, using the IKey parameter, and returns it to the respective record of the Query.

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 Dictionary Item, and returns it to the function calling record.

The Next If . . . Then statement checks whether the value in variable K = y or not. Variable y holds the total record count of the Query. If it is found 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 value in variable y.

This is necessary to initialize the Static Variables during the rerun of the same Query. In case of any change made on the Source Data before rerun, it will not reflect on the balance amount calculated earlier, because it will keep taking the ELSE route of the If . . . Then statement and retrieves the old value from 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