Running Difference

[chrisd9]

I am in the middle of converting a somewhat complex spreadsheet layout into a database. The spreadsheet consists of a large amount of raw data entry, and then a large amount of calculations.

The problem is, some of the calculations rely on absolute fields. I have figured a way around this using mutliple queries.

However, my problem is now that some of the calculations rely on repeating differences between two cells. For example I have dataset 1, 2, 3, and 4, and the the calculation for dataset 2 depends on the difference in "time" from 1 to 2 times some other value. then the next calculated value (for dataset 3) is the difference between 2 and 3 times some value.

What is the best way to complete this.

Thanks for all of your help in advance!

 

[SkipVought]

Chris,

It is usually not a good idea to store summary values in a table.

That is a REPORTING function, done at query/reporting time.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[chrisd9]

Skip,

This is exactly the question I meant to ask.... how do I perform that calculation through a query, when I want it performed on the same field across different data sets.

Thanks

 

[SkipVought]

Please give a simple concrete example.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[chrisd9]

k... thanks skip...

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 for your help, sorry for not being clear originally.

 

[SkipVought]

So is the "absolute value" the FIRST or MIN value in each Test Set?

ie do you measure times for test1 relative to .4 and test2 relative to .2?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[chrisd9]

.4, 5 and 8 would all be measured relative to 0. in other words .4 hours have passed since the experiment started, and 5 hours have passed since the experiment started.