Advanced Calculation Query??

[chrisd9]

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" (1 hour).

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 easily 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!

 

[ByteMyzer]

For elapsed time, you can try a query like this (the following assumes that your table name is Table1):

SELECT T1.[TEST ID], T1.[DATASET ID], T1.[TIME], 
ABS(T1.[TIME]-(SELECT T2.[TIME] FROM Table1 AS T2 
WHERE T2.[DATASET ID]=(SELECT MAX(T3.[DATASET ID]) 
FROM Table1 AS T3 WHERE T3.[TEST ID]=T1.[TEST ID] 
AND T3.[DATASET ID]<T1.[DATASET ID];);)) AS [ELAPSED TIME]
FROM Table1 AS T1;

 

[chrisd9]

ByteMyzer,

Thanks a lot for your help.

Where does T1, T2, etc come into it? I have one table that I am pulling data from.

Should I leave T1, T2, T3, as you have written them when I replace the table and field names with what mine actually are?

Thanks again

 

[ByteMyzer]

Where does T1, T2, etc come into it? I have one table that I am pulling data from.

T1, T2 and T3 are aliases for multiple instances of the same table. I recommend reading up on subqueries for more details.

Should I leave T1, T2, T3, as you have written them when I replace the table and field names with what mine actually are?

Yes, leave T1, T2 and T3 EXACTLY as I have written them.