Advanced Calculations?

[chrisd9]

Access Database Query Question
I am developing a database for a process for which we take samples at multiple times. So I have multiple processes (test1, test2, etc), with multiple samples for each test (-01,-02,-03)

I have a table of the form:

TEST ID........DATASET ID...........TIME.........OTHER DATA
test1.............test1-01...............4
test1.............test1-02...............5
test1.............test1-03...............8
test2.............test2-01...............2
test2.............test2-02...............4
test2.............test2-03...............9


My initial "raw data" table is of this form, (with multiple "other data" fields containing data of the samples taken at the particular time)

With all this raw data I need to calculate values. However, some of these values depend on the change in time between samples taken. For example, I need an automated way of calculating the time elapsed between dataset "test1-01" and dataset "test1-02" (4.6 hours).

I need to then be able to pull this value and use it in other calculations (a seperate query perhaps?).

The problem is I am having to recreate many calculations which were previously done in a spreadsheet. I have already found a way to mimic "absolute references" with multiple queries for the calculation in access. However, I am stuck on how to recreate this calculation.

THANKS IN ADVANCE!

 

[lespaul]

If you are going to be doing that many calculations, I would delve into the world of Access VBA Forum705.

With VBA you can create functions that will perform your calculations and you can use them in queries.

So check out modules, functions and code (oh my!)!

Leslie

 

[chrisd9]

thanks, leslie

will do

 

[MichaelRed]

If the recordset is properly normalized, you should be able to order it acording to the fields ([TEST ID];[DATASET ID];[TIME]) then, a subquery (self join to "previous record") would provide the [TIME] for both the previous and current records. This would permit the use of a simple datadiff function within the query to provide the interval betewwn each record pair. Of course it gets a bit more complex as you still need to differentiate the actual [TEST], to avoid the calc between test1 and test2 occurances.





MichaelRed
mlred@verizon.net

 

[willir]

...Other considersations. SAS and SPSS - these are statistical applications developed for this type of thing.