Allowing Modifiable Calculations Dynamically.

[travisdh]

Hi All,

I have asked much more detailed questions, but I am currently working on a lab based database system. I have lots of fields which will be used to store the values which is fine, however I need a way to be able to enter a calculation that would apply to a test, but be able to do it dynamically if that makes sense.

As there would be others using the system at the same time, I really really really want to be able to avoid having to recreate the database every time a new formula for a new test is created. It just doesen't really make it usable if I have to embed functions in a database.

The example I have is say I have a sample which has x amount of chemical on it, I then divide it by weight of sample measured, and by time tested for. Thats quite simple, but then there might be a modification to that formula to take into account something else, or instead of using weight it then accounts for Surface area, or a new test and I don't want to have to recreate it each time.

How do other people get around these problems without having to create new formulas in the tables etc and allowing the tables to have a formula entered in them (eg search for SampleID, and then look for ChemicalEmissions value and Divide it by SAMPLEID Weight, then by SampleID Time)

Thanks for all the help!

Regards,

Travis H.

 

[DCrake]

If the new formula superseeds the old formula then you could actully store the formula in a table and use the eval() to dervive the answer. The problem lies if you want to incude an additional formula. How would you tell the app to use the new formula because at the time of developing the app was not aware of any new formulas.

 

[gemma-the-husky]

if you did this in excel, you could write a simple cell formula to deal with the new equation you want to use.

You do exactly the same in access. The trouble is, in order to make access more rigorous, we dont expect users to do stuff like this, and in fact deliberately try to stop them doing it.

Now if you have the problem where the solution is "dynamic", it becomes exceedinglly difficult to find a way to give the user the ability to change things in a controlled way. All you can really do is add the functionality as it is required - but it can become quite a laborious and expensive process.

 

[travisdh]

Its not so much the changing formula's but rather being able to add new formulas as it is required. What I want to do is have a table that has tests that you enter, and pick the test analytes that are for that test and in another table there is results which basically have SampleID, Result Name, Result Value and Result Units.

What I want to then be able to do is specify in the test table, how the formula's are calculated, for example for test A, I need the analytes A,B,C,D,E,F & G which are calculated for example using Formula Analyte * Weight * Time Difference and the results are shown on the report.

Storing the value is not a major problem in that case, but the problem presents itself when I have a new test B that has results X,U,T,F & Q that have a different calculation to calculate a final result. And new tests are added on a regular basis so the problem is that. Not so much that the calculation gets changed, but rather that new calculations for new tests get added, and I want to be able to associate a new calculation with a new test by adding it to the database field, rather than redesigning it some way.

Is it possible to store a SQL query in a field, that for example does a lookup of the sampleID to return the applicable tests values and proforms a calculation on it.

For Example, Multiple Selects.

SELECT Results.Name, Results.SampleID, Results.Value, Results.Unit from Results WHERE SampleID = x, Then

Perform Calculation on data returned, eg one value is Weight = 2.3123, another is Time = 24 hours, another is Emission = 23 ug/Tube, so the test shows that result = Emission * Weight * Time, so it has a SQL Query that calculates that, but if there was a different test, it would use that query instead.

Does that make sense?

Thanks for any help!

 

[gemma-the-husky]

the problem is defining both the variables and calculations in a way that you can set up these parameters in tables(ie as form controls), and yet the access/sql engine can turn these into a query. The awkward thing is that the query will be different each time.

one way would be to have a table that stores
a) a description of what the query does and
b) the SQL for that query.

then you can send your users a text file/email etc including the SQL, and they can can cut and paste it into the table.

if you look at some the MS templates, they have a standard "report/query" picker that could easily be adapted to do this.

 

[Jason34]

Hello,

It sounds like you are trying to work with Dust HiVol or Dust Deposition Data?

 

[Jason34]

Hello Again,

It sounds like you are already using the ESdat database. Why wouldn't you just use the explicit support for those calcs already? It also has "Post Import Calcs" which you can create and run on imported data to generate "calculated" results which can then be stored. Why would you be wanting to import the ESdat lab files yourself and create your own reports and non-detect calcs rather than get the software to do for you, which it does? It sounds like you're spending a lot of time re-inventing the wheel. I'm sure the guys at ESdat would be a good source of information if you need it.