How to calculate the difference between values in preceeding records

[dmisen]

I have data on air pollution coming from sources across the country,
and control options for reducing pollution from each source. The
example below shows data for:
- 2 sources (plant_ID "001" and "008")
- total tons of pollution emitted from each source (20 and 25 tons,
respectively)
- an ID code for the each control device that could be applied to
reduce the emissions
- the control efficiency for each control device (percent that it
would reduce the emissions)
- the tons that each control device would reduce the emissions

I need to create an Access query that will calculate the incremental
tons reduced ("inc_red"), i.e., the difference between the tons reduced
by the device listed in the current record minus the tons reduced by
the device in the previous record, if the previous record is for the
same plant. Eg., the 1st control device is listed in record # 2 and it
would reduce emissions a total of 10 tons. The 2nd control device is
listed in record # 3 and it would reduce emissions a total of 10 tons.
The incremental difference is calculated by subtracting the reduction
for record # 2 from the reduction for record # 3, which equals zero
(because they are both 10). I have greatly simplified the problem, but
this gets at the essence of my problem. I have a database of over
100,000 records for which I need to do similar calculations, and the
database is updated regularly.

ID plant_ID total_tons_emitted control_ID CE tons_reduced
inc_red
1 001 20 no control
2 001 20 N01 0.5 10 10
3 001 20 N08 0.5 10 0
4 001 20 N03 0.8 16 6
5 008 25 no control
6 008 25 N04 0.4 10 10
7 008 25 N07 0.6 15 5

[Bob Quintal]

dm****@gmail.com wrote in
news:11**********************@u72g2000cwu.googlegr oups.com:

I have data on air pollution coming from sources across the
country, and control options for reducing pollution from each
source. The example below shows data for:
- 2 sources (plant_ID "001" and "008")
- total tons of pollution emitted from each source (20 and 25
tons,
respectively)
- an ID code for the each control device that could be
applied to
reduce the emissions
- the control efficiency for each control device (percent
that it
would reduce the emissions)
- the tons that each control device would reduce the
emissions

I need to create an Access query that will calculate the
incremental tons reduced ("inc_red"), i.e., the difference
between the tons reduced by the device listed in the current
record minus the tons reduced by the device in the previous
record, if the previous record is for the same plant. Eg., the
1st control device is listed in record # 2 and it would reduce
emissions a total of 10 tons. The 2nd control device is listed
in record # 3 and it would reduce emissions a total of 10
tons. The incremental difference is calculated by subtracting
the reduction for record # 2 from the reduction for record #
3, which equals zero (because they are both 10). I have
greatly simplified the problem, but this gets at the essence
of my problem. I have a database of over 100,000 records for
which I need to do similar calculations, and the database is
updated regularly.

ID plant_ID total_tons_emitted control_ID CE
tons_reduced inc_red
1 001 20 no control
2 001 20 N01 0.5 10
10 3 001 20 N08 0.5 10
0 4 001 20 N03 0.8
16 6 5 008 25 no control
6 008 25 N04 0.4 10
10 7 008 25 N07 0.6 15
5

You could use a subquery to return the preceding record, based
on matching plant_ID, Control_ID, and smaller ID or entrydate.

SELECT * from table,
table.Tons_reduced - (SELECT last(tons_reduced)
FROM table ALIAS Subtable
WHERE subtable.Control_ID = table.control_id
AND subtable.plant_ID = table.plant_ID
AND subtable.ID < table.ID) as inc_red
FROM table;

-
Bob Quintal

PA is y I've altered my email address.

[John Mishefske]

> You could use a subquery to return the preceding record, based

on matching plant_ID, Control_ID, and smaller ID or entrydate.

SELECT * from table,
table.Tons_reduced - (SELECT last(tons_reduced)
FROM table ALIAS Subtable
WHERE subtable.Control_ID = table.control_id
AND subtable.plant_ID = table.plant_ID
AND subtable.ID < table.ID) as inc_red
FROM table;

Unless I misunderstood the post I don't think this is going to
work for a couple of reasons. The Last() function doesn't give
you the last record and without an ORDER BY clause the order
returned by the database is undefined and cannot be depending on.

Instead a solution is to look for the previous value with a
SELECT TOP 1 query (which is not portable to other DBMS systems -
it is a JET only feature).

SELECT * from table As t,
table.Tons_reduced - (SELECT TOP 1 tons_reduced
FROM table ALIAS Subtable
WHERE subtable.Control_ID = table.control_id
AND subtable.plant_ID = table.plant_ID
AND subtable.ID < t.ID
ORDER BY subtable.ID Desc) as inc_red
FROM table;

The Tons_reduced field could perhaps be substituted in the
subquery's "AND subtable.ID < t.ID" instead of ID.

--
'---------------
'John Mishefske
'---------------

[Lyle Fairfield]

John Mishefske wrote:

Instead a solution is to look for the previous value with a
SELECT TOP 1 query (which is not portable to other DBMS systems -
it is a JET only feature). From Ms-SQL Books on Line:

"You can use the TOP clause to limit the number of rows that are
returned in the result set.

TOP ( expression ) [ PERCENT ] [ WITH TIES ]

expression is a numeric expression that specifies the number of rows to
be returned; or if PERCENT is specified, the percentage (specified by
expression) of the result set rows is returned. For example:

TOP (120) /*Return the top 120 rows of the result set. */
TOP (15) PERCENT /* Return the top 15 percent of the result set. */.
TOP(@n) /* Return the top @n rows of the result set, with the variable
declaration: DECLARE @n AS BIGINT; SET @n = 2 */."

[Bob Quintal]

John Mishefske <jm**********@SPAMyahoo.com> wrote in
news:RB*****************@tornado.rdc-kc.rr.com:

You could use a subquery to return the preceding record, based on matching plant_ID, Control_ID, and smaller ID or entrydate.
SELECT * from table,
table.Tons_reduced - (SELECT last(tons_reduced)
FROM table ALIAS Subtable
WHERE subtable.Control_ID = table.control_id
AND subtable.plant_ID = table.plant_ID
AND subtable.ID < table.ID) as inc_red
FROM table;
Unless I misunderstood the post I don't think this is going to
work for a couple of reasons. The Last() function doesn't give
you the last record and without an ORDER BY clause the order
returned by the database is undefined and cannot be depending

on.
Instead a solution is to look for the previous value with a
SELECT TOP 1 query (which is not portable to other DBMS systems - it is a JET only feature).

SELECT * from table As t,
table.Tons_reduced - (SELECT TOP 1 tons_reduced
FROM table ALIAS Subtable
WHERE subtable.Control_ID = table.control_id
AND subtable.plant_ID = table.plant_ID
AND subtable.ID < t.ID
ORDER BY subtable.ID Desc) as inc_red
FROM table;

The Tons_reduced field could perhaps be substituted in the
subquery's "AND subtable.ID < t.ID" instead of ID.

Interesting observation: you correctly picked up I had an error
in my code, but offered a different correction, then a third
variant on the theme..

I typed last() instead of the intended max()

Again we prove that there's more than one way...

--
Bob Quintal

PA is y I've altered my email address.