Storing Formulas

I have an Access database in which I need to store some formulas. I have two tables; tblResults and tblTests. The relationship between the two is that some combination of Tests make up a result. For example:

Result1 = Test3
Result2 = Test1/Test4
Result3 = Test2+Test3
Result4 = (Test2+Test1)/Test3

For practical purposes, the maximum number of tests for a result will be four, although if it could be unlimited, that would be OK too. The only operators are the big four and parentheses. The users need to be able to create new Results, new Tests, and define the formula that ties them together.

What would be really cool is if I could invoke the Expression Builder and limit it to tblTests and the math operators I want. I don’t think I can do that, though. At least I haven’t figured out a way.

Another thought is to store the formula as a string. The user would enter a string and the program would validate it. Every time the result is computed, the string is parsed and ‘field names’ are replaced with values. This would be the easiest to program (maybe) but it would be slow and prone to errors in the user entry.

I could tokenize everything, which is essentially creating my own stripped down Expression Builder and store it as

Result4 “(“
Result4 “Test2?
Result4 “+”
Result4 “Test1?
Result4 “)”
etc…

It would be easier to control what was a field and what was an operator. I suppose if I stored it as a string, my parsing procedure would essentially be doing this, so I might as well store it this way anyway.

Then I have to be able to evaluate it, which seems tough. One idea I had was to create a new Excel worksheet, name some ranges the same names as the tests, insert some values into those ranges, then paste in the formula string and read the result. So I’d name three cell Test1, Test2, and Test3, put values in each of those cells, and paste =(Test2+Test1)/Test3 into an empty cell. The result of that cell would be my return value. I’d have to make sure that the field names were valid Excel names, but that shouldn’t be too hard.

The Excel route seems like overkill, but I don’t really want to create my own expression engine either. Am I missing something obvious here? How would you approach it?

Posted in Uncategorized

9 thoughts on “Storing Formulas

  1. Don’t build an expression parser. I’d go with creating the worksheet with named values, but use the Evaluate method to calculate the result rather than using a formula and getting the result.

  2. Interesting problem. I’m a little confused, though, about the tables and relations. Do you mean that you have a table of test values:

    key: Test1
    value: 1

    key: Test2
    value: 100

    key: Test3
    value: 200

    etc.

    And another table with formulas that reference these test values:

    key: Result1
    formula: Test3

    key: Result2
    formula: Test1/Test4

    key: Result3
    formula: Test2+Test3

    etc.

    And that you wish to evaluate the formulas and produce a result?

  3. Sorry for the confusion Eric. It’s actually a little more complicated than that, but essentially that’s it. I don’t have anything but the result name in tblResult, but that’s just because I haven’t figured out what to do yet.

  4. Okay. Well, if we have a table named Tests with two columns, Key and Value, containing these values:

    Key, Value
    Test1, 1
    Test2, 100
    Test3, 200
    Test4, 30

    And a table named Results with one column, Formula, containing these values:

    Formula
    Test3
    Test1/Test4
    Test2+Test3
    (Test2+Test1)/Test3

    Then, if we create and save a crosstab query of the test values and name it TestValues:

    TRANSFORM First(Tests.Value) AS testvalue
    SELECT “test”
    FROM Tests
    GROUP BY “test”
    PIVOT Tests.Key;

    Then we can run the following query to evaluate the formulas:

    SELECT Results.Formula, DLookUp([Formula],”TestValues”) AS Result
    FROM Results

    Using the test data and result formulas above, this query produces:

    Formula, Result
    Test3, 200
    Test1/Test4, 3.33333333333333E-02
    Test2+Test3, 300
    (Test2+Test1)/Test3, 0.505

    How’s that?

  5. Worth looking at the Access EVAL function which will evaluate VBA expressions as opposed to the Excel EVALUATE method which evaluates Excel formulae.
    I would have thought you could build a Wizard that generated a text string formula that you could check with EVAL and then maybe pass to an Access UDF containing EVAL or possibly just embed in the SQL.

  6. using as he said the eval function and a table for your tests, you could have unlimited number of test(n) and values related to test(n)

    I would throw all that on a form and run queries through VBA to get my results to mimic a bit the expression builder.
    only drawback is you dont have auto complete nor help as you type

  7. Eric gets my vote. I hate the Dlookup but in this case it seems to work quite effectively.
    Ordinarily I would just write the VBA and trawl through. You could make this a function and call it in the query but that would end up even slower than the Dlookup.

  8. Dick,

    From experience (we handle very complex spreadsheets and their data in sql) the longer term question is what you want to be able to do with the results. If you are looking for value added on the underlying data, for example the ability to calculate the aggregate of a complex formula involving divisors, then some form of Excel parsing and the use of Excel as a maths engine is the way forward, but this requires complex dependency information for each formula and a pretty complex output handler.

    If looking for simple results that can be handled with database logic, as Eric’s solution might suggest, don’t got the Excel route. The further you dig the worse it gets…

  9. If I run the TestValues query prepared by Eric, I get the following error message:
    Microsoft jet database engine does not recognize “”test”” as a valid field name or expression.

Leave a Reply

Your email address will not be published. Required fields are marked *