Using Recursion in your Access database

Today’s guest post come’s from Mark Davis, a Access MVP. You can learn more about him at http://www.linkedin.com/in/markgerarddavis

At its simplest definition, recursion is a function that is applied to itself, or a function that is called by itself.  Here is a basic example, taken from mathematics, of a factorial.  5! is expressed as 5x4x3x2x1, or 120.  To compute a factorial for the number n, the following simple function could be utilized:

Public Function factorial(n as Integer) AS Integer
            If n <= 1 then
                        Factorial = 1
            Else
                        Factorial = n * factorial(n-1)
            End if
End Function

The logic for this function is as follows: We will use 3 as our n parameter value.  Upon first call, we have 3*factorial(2).  Factorial(2) is 2*Factorial(1).  Factorial(1) is 1.  We therefore have 3*2*1, or 6.

Recursion within the scope of say, a laboratory information management system, is used to build complex formulas, which are based on other formulas.  For example, if we have a formula for Copper Dry Basis that is CuWt*(1-Moist), where CuWt is a formula that is (CuPost-CuTare)/CuWt, and we try to parse out say, a Copper Dry Basis formula, first the parsing function is called.  The first element it sees might be a CuWt formula.  The parsing function is again called, this time with the formula id for CuWt.  (CuPost-CuTare)/CuWt as a string is returned (with relevant values, of course).  The original call to the parsing function is now active again, and we continue processing the additional elements, ‘*)1-‘, Moist, and ‘)’ .

These recursions are built into the formula models, allowing ultimate flexibility when building formulas.  Though we could technically have the formula model exist without recursion, it would make the creation and application of some formulas near impossible to enter in the correct syntax.  As an example, the verbose display of the Error % formula for one process could easily be 1527 characters long, including all the appropriate bracketing and operands.  For the manager to be expected to correctly program that formula would be absurd.  Because of recursion, the formula can be simplified to it’s very base form, a percentage difference formula, which is only 64 characters in length.

A special note for consideration is that there must always be a final point of termination for recursive functions.  For the sake of this discussion, that point of termination is when there are no longer any elements to process for the current formula, specifically elements of the ‘formula’ type.  In our above factorial example, we halt processing when we get to the integer 1, as defined by the mathematical definition of a factorial.  Not providing a final point of termination results in a similar scenario to an infinite loop, causing memory leaks and a whole bunch of bad stuff that you really don’t want to deal with.  As a matter of practicality, a mathematical formula cannot be based on itself.  So, the formula for Copper Dry Basis cannot be based on the formula for Copper Dry Basis.  When editing formula compositions by hand (you should NOT do this and instead should use the formula builder – a separate form and base table used to construct the formulas).

For more information on the concept of recursion, as well as programming examples, please see the following resources:

http://en.wikipedia.org/wiki/Recursion#Recursion_in_computer_science

http://erwnerve.tripod.com/prog/recursion/magic.htm (geared toward java, but can be adapted easily)

About

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Posted in Access Help

Free email subscription

Enter your email address:


Facebook

Twitter

Blog Archives

ITImpact
%d bloggers like this: