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?
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.
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?
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.
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?
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.
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
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.
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…
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.