subtract records from different rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a table with temperature collected every minute. So I have a row of
time and a row of temp. I would like to subtract the temperature of time 1
from time 2 and so on. Is there a way to subtract the previous record in the
same row?

I have done it in excel by referencing the next cell in an expression but I
need to do it to 500,000 records.

Thank you
 
Try something like:

SELECT TheTime, TheTemp, TheTemp - (SELECT Top 1 TheTemp FROM tblTemps T
WHERE T.TheTime < tblTemps.TheTime ORDER BY TheTime Desc) as TempDiff
FROM tblTemps;
 
Thanks for the help Duane,

I ran your suggestion but it is not exactly what I need. In general I need
to subtract 2 records in one field. I need to go through a column of values
and subtract each record from the one before it while creating anther column
with the subtracted values.

I hope this makes more sense.
Thanks
again.
 
Why is my suggestion "not exactly what exactly what I need"? Please post
your table and field names and some sample records as you would expect to
see them in your query results.
 
The table I am starting with is as follows: I need to subtract 6.3358 from
6.3636 into a new record in a new field; then subtract 6.3636 from 6.3482 and
so on.....

Date Time Temperature
25-Feb-03 14:43:30 6.3358
25-Feb-03 14:43:35 6.3636
25-Feb-03 14:43:40 6.3482
25-Feb-03 14:43:45 6.3588
25-Feb-03 14:43:50 6.3671
25-Feb-03 14:43:55 6.3792
25-Feb-03 14:44:00 6.3862
25-Feb-03 14:44:05 6.3284
25-Feb-03 14:44:10 6.3311

The resulting table(query) should look like this:

Date Time Temperature TempDiff
25-Feb-03 14:43:30 6.3358
25-Feb-03 14:43:35 6.3636 0.0278
25-Feb-03 14:43:40 6.3482 -0.0154
25-Feb-03 14:43:45 6.3588 0.0106
25-Feb-03 14:43:50 6.3671 0.0083
25-Feb-03 14:43:55 6.3792 0.0121
25-Feb-03 14:44:00 6.3862 0.007
25-Feb-03 14:44:05 6.3284 -0.0578
25-Feb-03 14:44:10 6.3311 0.0027

Thanks again, your help is much appreciated.
 
Hi,


Duane's solution should have worked, except for the first record (which has
no "previous" record.


SELECT a.DateTime, LAST(a.Temperature),
LAST(a.Temperature)-LAST(b.Temperature)
FROM ( myTable As a INNER JOIN myTable As b
ON a.DateTime > b.DateTime) INNER JOIN myTable As c
ON a.DateTime > c.DateTime
GROUP BY a.DateTime, b.DateTime
HAVING b.DateTime=MAX(c.DateTime)



should do too.

Since the aggregate LAST is applied to a single record, it can be replaced
with MIN, MAX, or by FIRST (and even by SUM).



Hoping it may help,
Vanderghast Access MVP
 
OK, I finally figured both solutions and got them to work. I am not
proficient in SQl and Access 2003 does not like the "<>" signs in design view
(which is how I run my queries).

Unfortunately, both versions (Duanes and Michels) take a long time on a
large dataset and crash my machine. I will run them in batches.

Thanks very much for the help and patience.
AK
 
Back
Top