calculation between 2 diff. rows

[eiffeltower]

hi everyone, i have a table of timeseies data and would like to run a query working out the difference between the current row's value (day) and a previous row (day, or days before) and put this difference in a new column in the resulting query.


I know it would be straight forward in Excel to run a formula between 2 different rows, but this table has >1 million records and are appended with new records regularly.

Any help or inspiration would be greatly appreciated!

 

[Dennisk]

Either use a sub query to pick up the rows prior to the current row. (Rather like a Sequence Query) or use code to read each row perform the calculation and create a temp table and query that.

 

[Ian Mac]

If you are using dates, the following might be helpful:

http://www.access-programmers.co.uk/forums/showthread.php?p=621177#post621177

Cheers,

 

[Kinger43]

Using calculation between rows as criteria

I have a similar problem where I need to compare a value in the current row with the previous row (the previous date not necissarily the day before). I need this to be the criteria. If the value has increased then I want to see that row. Is a sub query still necessary or is there some sort of previous record command?

 

[Rabbie]

It is possible to do clever stuff if you drag the data source twice onto the Query Builder and then you can select a previous row for use in a calculation with dextorous use of the criteria. Iam work so I cannot get hold of the example just now but I will look tonight.

 

[Ian Mac]

Can you post exactly what the row before might contain compared to the current row, I'm fairly confident my link can be changed to suit.
If you can provide an example of the query without the calculated 'row before' data, that would be helpful.

All it is doing is taking the Date of the current row and looking for the next date below it, can be applied to numbers if they are incremental or decremental. As rabbie says, 'dextorous use of the criteria'.

Unless there's something like this to latch onto, code is going to be the answer.
Rememeber just because you see the Table as you do doesn't mean that it is ordered like that, so there needs to be a way to order the data so the row before is actually the row before you want.

Cheers,

 

[Kinger43]

Query uses two tables:
daily_extract with fields [Meter_No, Phase, Blinks, Reading_Date, Reading_time]
CALLNET with fields [Meter_No, Account_No, Line_Pole_No, Phase, Name]
The query is concerned with the daily_extract.Blinks. I need the query to return the record whenever Blinks (a number between 0 and 250) changes (it can only go up). The daily_extract table is appended with new records daily, however that doesn't mean there is a record everyday. Basically there are like 13,500 accounts which get a new entry everyday. I need to look at the previous date for a particular Account_No or Meter_No (as these numbers are unique per account) and check to see if Blinks has changed.

 

[Kinger43]

I forgot to mention, the criteria in the Reading_Date Field states (Between [startdate] and [endate]). This is such that the user can specify the date range in which the query should look. I am only concerned with dates in that range.

 

[KeithG]

copy the below into you query and let me know if it brings back the blinks from the previous date.

Previous_Blinks:Dlookup("Blinks","daily_extract","Reading_Date=#" & DMax("Reading_Date","daily_extract","Reading_Date>#" & daily_extract.Reading_Date & "#") & "#")

 

[Kinger43]

It is bringing back something, sometimes it is the blinks from the previous date and sometimes it is way off, thanks for the tip, I'll work with it and see what I can do.

 

[KeithG]

if you can post an example I can take a look.

 

[Kinger43]

Does the # have some significance or is it just a placeholder?

 

[KeithG]

In access you must surround a date value with # (#date#)

 

[Kinger43]

What do the & symbols mean?

 

[KeithG]

concatenation of strings