Numerical Integration Query

[emorris1000]

I need to do a numerical integration of some data, which is a breeze in Excel, but I can't figure out how to do it in Access. Here's the basic table

ID Time Value
1 1 2
2 1.4 3
3 1.7 6
4 2.1 4

What I want to do is to calculate a "dT" value that I can then multiply by the Value field.

It would look something like

dT Value dT*Value
1 2 2
0.4 3 1.2
0.3 6 1.8
0.4 4 1.6

and so on. Anyone have any idea how to do this in a single query? I think I could do it in a series of queries, but that's not efficient (involves ~100k records). I think I could do it in VBA as well, but I am sure there is an easier way to do this, which would be great because I am going to need to do this in a bunch of places.

 

[DCrake]

Use a query

dT:[Time]*[Value]

But don't have names like Time and Value as they are Access Reseved words.

 

[emorris1000]

K, should have made it clear what dT means, it will be the difference in time from one record to the next.

So, when the time values are:

0.5
1.2
1.7
2.1
2.5

The dT values should be

0.5
0.7
0.5
0.4
0.4

Time*Value is, on it's own meaningless because the Time is cumulative and the Value is snaphot (temperature at said time.) I have to numerically differentiate Time to dT, then use that to numerically integrate value over dT.

The index field will define the sequence used for the differentiation, as the subsequent record will always have an index of +1 the previous record.

And yeah, those terms are reserved, just using them because they are clear. The actual fields aren't going to have much meaning to anyone but me.

 

[Brianwarnock]

I think this is one of those situations where you have the table twice in the query so that you can say something like Table1.time-table1_1.time where Table1,ID=table1_1.id+1

Unfortuantely I haven't got time to play right now.

Brian

 

[emorris1000]

Yeah I think you are right. I stumbled across this link while looking for an answer:

http://allenbrowne.com/subquery-01.html

the section "Get the value in another record"

seems to have just what I need. Their example is:

SELECT MeterReading.ID, 
MeterReading.ReadDate, 
MeterReading.MeterValue, 
   (SELECT TOP 1 Dupe.MeterValue                 
   FROM MeterReading AS Dupe                     
   WHERE Dupe.AddressID = MeterReading.AddressID 
     AND Dupe.ReadDate < MeterReading.ReadDate   
   ORDER BY Dupe.ReadDate DESC, Dupe.ID)          AS PriorValue 
FROM MeterReading;

Now, I tried to copy this concept and did the following:

SELECT TestTable.ID,  TestTable.Time, 
(select Dupe.Time, Dupe.ID
From TestTable AS Dupe
where dupe.id = (TestTable.ID+1))
As NextTime
From TestTable;

which gave me the following error

"You have written a subquery that can return more than one field without using the Exists Reserved word...."

Still trying to hammer it out but I think this is the first step.

Edit:

SOLVED

SELECT TestTable.ID,  TestTable.Time as CurTime, 
(select TOP 1 Dupe.Time
From TestTable AS Dupe
where dupe.Time < TestTable.Time
Order by Dupe.ID desc)
As PrevTime
From TestTable;

Gives me the index, the time, and the previous time. Now I just need to combine it all into a monstrosity of a query