how to calculate the field delta between two records in the same table

Hi,

My recent posts have all been related to one single (and I though
simple) problem. Basically I need to calculate the delta for a given
field using the current record and the previous record. The table must
be time ordered for the deltas to be calculated correctly. Right now
I'm trying to do this from ADO using SQL (e.g. open connection, do SQL
via conn.execute "SQL goes here").

I've tried a couple of different approaches. One came from SQL
Fundamentals book by John J Patrick:

************************************************** ************
-- 16-11 Access SQL: Step 1

select price,
description
into sec1611
from l_foods
where price 1.75
order by price,
description;

-- 16-11 Access SQL: Step 2

alter table sec1611
add column line_number counter;
************************************************** ************

This seems to work most of the time, but not always. I believe that it
has to do with the fact that the alter column... is messing with the
order by from the select statement. He says in his book that you can
rely on the order of tables after select...order by with Access but
I've proved that this is not true.

My next attempt involved a self join....something similar to the
following:

************************************************** ************
SELECT YourTable.YourDateField, (Select First(YourDateField) _
as NextDate from YourTable as [Temp] WHERE _
[Temp].[YourDateField] [YourTable].[YourDateField]) _
AS NextDate FROM YourTable _
ORDER BY YourTable.YourDateField
************************************************** ************

Once you have the new column with NextDate, its easy to subtract
YourDateField from NextDate. This too seems to work most of the time
too, however sometimes NextDate is not updated correctly.

I'm currently at a loss as to how to perform a delta calculation on a
field in a table that is robust and will work 100% of the time.

I'm not looking for someone to write a solution for me....but more
some help and pointers as to what could be going wrong and possible
places to look for solutions. I've combed the internet using Google
for hours and found nothing that seems any better than the examples
above.

Any help would be greatly appreciated :-)

charlie

Try using MIN instead of FIRST. FIRST returns a more or less random
record - the first one the database engine finds in the group of records
that are returned. Some people claim that it is the first record in disk
order, but I'm not sure if that is the case.

SELECT YourTable.YourDateField
, (Select MIN(YourDateField)
FROM YourTable as [Temp]
WHERE [Temp].[YourDateField] [YourTable].[YourDateField])
AS NextDate
FROM YourTable
ORDER BY YourTable.YourDateField

Of course, you may have other criteira to add to the where clause depending
on your need to get a date time that is associated with other fields in your
table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"charlie" wrote in message
ups.com...
Hi,

My recent posts have all been related to one single (and I though
simple) problem. Basically I need to calculate the delta for a given
field using the current record and the previous record. The table must
be time ordered for the deltas to be calculated correctly. Right now
I'm trying to do this from ADO using SQL (e.g. open connection, do SQL
via conn.execute "SQL goes here").

I've tried a couple of different approaches. One came from SQL
Fundamentals book by John J Patrick:

************************************************** ************
-- 16-11 Access SQL: Step 1

select price,
description
into sec1611
from l_foods
where price 1.75
order by price,
description;

-- 16-11 Access SQL: Step 2

alter table sec1611
add column line_number counter;
************************************************** ************

This seems to work most of the time, but not always. I believe that it
has to do with the fact that the alter column... is messing with the
order by from the select statement. He says in his book that you can
rely on the order of tables after select...order by with Access but
I've proved that this is not true.

My next attempt involved a self join....something similar to the
following:

************************************************** ************
SELECT YourTable.YourDateField, (Select First(YourDateField) _
as NextDate from YourTable as [Temp] WHERE _
[Temp].[YourDateField] [YourTable].[YourDateField]) _
AS NextDate FROM YourTable _
ORDER BY YourTable.YourDateField
************************************************** ************

Once you have the new column with NextDate, its easy to subtract
YourDateField from NextDate. This too seems to work most of the time
too, however sometimes NextDate is not updated correctly.

I'm currently at a loss as to how to perform a delta calculation on a
field in a table that is robust and will work 100% of the time.

I'm not looking for someone to write a solution for me....but more
some help and pointers as to what could be going wrong and possible
places to look for solutions. I've combed the internet using Google
for hours and found nothing that seems any better than the examples
above.

Any help would be greatly appreciated :-)

charlie