How can I integrate advanced computations into a database field?

Question

My biological research involves the measurement of a cellular structure as it changes length throughout the course of observation (capturing images every minute for several hours). As my data sets have become larger I am trying to store them in an Access database, from which I would like to perform various queries about their changes in size.

I know that the SELECT statement can incorporate some mathematical permutations, but I have been unable to incorporate many of my necessary calculations (probably due to my lack of knowledge). For example, one calculation involves determining the rate of change during specifically defined periods of growth. This calculation is entirely dependent on the raw data saved in the table, therefore I didn't this it would be appropriate to just calculate it in excel prior to entry into the field.

So my question is, what would be the most appropriate method of performing this calculation. Should I attempt to string together a huge SELECT calculation in my QUERY, or is there a way to use another language (I know perl?) which can be called to populate the new query field?

I'm not looking for someone to write the code, just where is it appropriate to incorporate each step. Also, I am currently using Office Access but would be interested in any mySQL answers as I may be moving to this platform at a later date. Thanks all!

Answer 1

You could encapsulate your logic and maths into a custom function in VBA and then call that in your select statement. This methodology would also work with other database engines but the exact wording might be slightly different

Answer 2

If you need to populate a field with the result of a complex calculation, it will be easier to do the calculation in a full-fledged programming language, such as Perl, instead of trying to do it in SQL.

Perl has a very good database API, "DBI", with drivers for just about every database engine known to man. Here's a good short article on DBI:

http://www.perl.com/pub/a/1999/10/DBI.html

Answer 3

You would be at the mercy of the SQL implementation for calculation options, precision, etc. Better to use a separate language where you have control, extensibility and flexibility to store the results you want and need.

Answer 4

Doing it in SQL will be a lot faster, however much harder to debug (I'm guessing that you're looking at things like ANOVA, t-tests, chi^2 etc).

Having said that, you may want to to store and calculate interim values like the delay since the previous measurement, and the change in measurement.

OTOH, the metrics you describe are very simple to do in SQL:

one calculation involves determining the rate of change during specifically defined periods of growth

C.

Answer 5

I'd propose that you use Access as a front-end tool for entering, editing and printing your data. You can store the data in any back-end database engine (MySQL, SQL Server, etc.), though Jet/ACE (the default Access database engine) is likely to be completely adequate unless your data set gets very, very large (it's limited to 2GBs but you don't really want to continue using it if your data grows to much over 1GB during regular usage).

For complex statistical analysis, though, I'd recommend considering exporting the data and using a proper statistics package for doing the analysis. This means your reporting might all be done from there.

In that situation, you could leverage Access's capabilities in allowing you to create an interface for selecting the datasets you wanted to export for analysis. The last time I did this for a client, they were using SPSS for the data analysis and I built them a very flexible export interface (they could choose any variables they liked for analysis).

Whether this is a helpful alternative depends on the extent and type of the analysis you're going to do. If you're using a lot of functions that Access VBA lacks and have to borrow them from Excel or write replacements for them, then you might be better off doing all of that in some other program.

Also, it may be that some or many or all of your calculations belong in the presentation layer and not in SQL. Access reports have a lot of capabilities here, and if you're summarizing data, it may be best done at that level, rather than in the SQL recordsources underlying your reports.