434,796 Members | 1,240 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,796 IT Pros & Developers. It's quick & easy.

# How to calculate the difference between values in preceeding records

 P: n/a 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 Feb 25 '06 #1
4 Replies

 P: n/a 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. Feb 25 '06 #2

 P: n/a > 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 '--------------- Feb 26 '06 #3

 P: n/a 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 */." Feb 26 '06 #4

 P: n/a John Mishefske 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. Feb 26 '06 #5

### This discussion thread is closed

Replies have been disabled for this discussion.