Using previous record value successively in calculations

[carvind]

Friends,

I am trying to create a query where I need to use the value of a field in record #1 to calculate the same field in record #2 and value in record #2 to calculate the same field in record #3 and so on. Can someone provide guidance on how to accomplish this? Thanks for your help.

 

[KenHigg]

This is going to be hard to explain but doing calculations across records where there is no relationship between the records except where they fall in reocord sort may not be a good idea. Perhaps you can elaborate?

 

[jzwp22]

Typically you would use a subquery to pull in the value from the previous record. You mention that your records are in order by record number. Do you control the record number or is that value the result of an autonumber field? I ask because if records are deleted over time, the previous record may not have a record number that is 1 less than the current record.

The query with subquery would look something like this:

SELECT yourtable.recordnumber, yourtable.somefield, (SELECT Q1.somefield FROM yourtable as Q1 where Q1.recordnumber=yourtable.recordnumber-1) as PreviousValue
FROM yourtable

 

[carvind]

Actually the ID is sequential but descending order beginning with #25. The second will have the ID #24 and so on. I need to use the value corresponding to #25 in #24 and #24 in #23 and so on. Thanks

 

[KenHigg]

Are you doing a running sum type thing?

 

[jzwp22]

If you haven't done so already, you just need to alter the query I provided slightly. You will have to make sure you order the records appropriately in the main query

SELECT yourtable.recordnumber, yourtable.somefield, (SELECT Q1.somefield FROM yourtable as Q1 where Q1.recordnumber=yourtable.recordnumber+1) as PreviousValue
FROM yourtable
ORDER BY yourtable.recordnumber DESC

 

[carvind]

Thanks for your response. This subquery pulls the data value from previous record but I need to use it to calculate the same field in the next record and so on. How can I do it? Thanks

 

[jzwp22]

I'm not sure what you mean by "...calculate the same field in the next record". Can you provide an example of what you expect the query output to look like for 3 records or so? This will allow us to see what calculations you are doing.

 

[carvind]

Assume we have three records.

RECORD id Value

25 1.2345
24 value1
23 Value2
Here value1 needs to be calculated as 1.2345 * 4.75

and value 2 to be calculated as value1 * 3.25

The key point is I know the value only for the record ID 25 which 1.2345 and the other values have to be calculated using value in the previous record. Hope this helps. Thanks for your prompt response.

 

[Thales750]

You need to make a loop, this is not a job for a query

 

[carvind]

Can you help me with an example? Thanks

 

[jzwp22]

Actually based on your example, the value of 1.2345 is a constant throughout and it is the other numbers (4.75 & 3.25) that actually determine the value since you are doing multiplication. The following would yield the same values in the scenario you presented.

25 1.2345
24 1.2345*4.75
23 1.2345*4.75*3.25
I assume the next record would look like this:
22 1.2345*4.75*3.25*x
where x is some other value.



I think we need to get an idea of the bigger picture of what you are trying to do and from where the data are coming:

What are the structures of the key tables in your application? How are they related?

Where are those other numbers (the 4.75 & 3.25 etc.) stored?


What are you going to do with the values once you calculate them? (remember, in general, calculated values should not be stored in a table but rather calculated on the fly when you need them).

Are any other arithmetic operations needed or just the multiplication?

As Thales750 mentioned, I think you will need a custom function that includes a loop, but we need some more information on your overall application first.

 

[KenHigg]

This is going to be hard to explain but doing calculations across records where there is no relationship between the records except where they fall in reocord sort may not be a good idea. Perhaps you can elaborate?

.............

 

[sooty101]

It almost sounds similar to where you have from and To electricity readings where you have to calculate the difference then calculate by the unit value.
From 00000 units to 00250 Units

In the next record the "To" reading would be the "from" Reading
From 00250 To XXXXX
Is this something similar to what you are trying to achieve

 

[Thales750]

If you are talking about a small number of records after you apply a criteria. You cna eport it to Excell and it will be easy create A formula there.