Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Diminishing Balance Calc in Query


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
    'close recordset and remove the database objects
    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)
   '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

Exit Function

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:

  1. A Unique Value Field (Numeric or String Values) is the first parameter.  The parameter is declared as a Variant data Type.
  2. The Unique Value’s Field Name is the second parameter in String format.
  3. The Loan Installment Value Field Name.
  4. The Query Name is the fourth Parameter.
  5. 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.

    1. 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.
    2. 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 notVariable 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.

    Dictionary Object Links.

    1. Dictionary Objects Basics
    2. Dictionary Object Basics- 2
    3. Sorting Dictionary Object Keys and Items
    4. Display Records from Dictionary
    5. Add Class Objects as Dictionary Items
    6. Update Class Object Dictionary Item

No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code