How to compute difference between successive records?

[hstijnen]

Hi,
I've an Access db with a table that records the state of an electricity meter on successive moments, say every last day of month. Now I want to compute the electricity use over the months. In order to do that I have to sort the records by date and loop through the table and compute for each record the difference with the foregoing record.

How can I do that in VBA for MS-Access 2000? I'm rather new to it. I have developed a form to edit the records and would like to put the code under a button in this form.

Can anyone help me?

Henk

 

[PHV]

Any chance you could post the table(s) schema ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hstijnen]

The schema is very simple: Emeter(id, date, value, diff).
In the form frm_Emeter the user selects the emeter(id) and fills in the data for date and value. Then the VBA (re)computes the diff's.
I would think a bit as follows:

frmEmeter.requery 'ensures the records are in the correct order
goto second record
while not EOF then
'compute value minus value in foregoing record
frmEmeter.diff = frmEmeter.value - frmEmeter[-1].value
next record
end while

Or rather something like that directly on the underlying table Emeter. I'm looking for some basic piece of code to loop through a table in the db.

 

[MichaelRed]

look into "Self Join", in particular, if you create an Index monotomically increasing by one) in the date orderm then the two records are related by the index and the increment, so the two values are then available in the same record and the change is just the calculated field / difference.



MichaelRed

 

[hstijnen]

Thanks for your help!
I've found some code at msdn and now my code is something like

Sub BerekenVerbruik()
  Dim conDatabase As ADODB.Connection
  Dim rstMstanden As ADODB.Recordset
  Dim strSQL As String

  Set conDatabase = CurrentProject.Connection
  strSQL = "SELECT emeter_code, datum, stand, verbruik FROM meterstand order by emeter_code, datum"

  Set rstMstanden = New Recordset
  rstMstanden.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

  With rstMstanden
    Dim stand0 As Integer
    Dim code0 As String
    code0 = ""
    Do While Not .EOF
       If !emeter_code <> code0 Then
          code0 = !emeter_code
          !verbruik = -1
       Else
          !verbruik = !stand - stand0
       End If
       stand0 = !stand
       .Update
       .MoveNext
    Loop
  End With

rstMstanden.Close
conDatabase.Close

Set rstMstanden = Nothing
Set conDatabase = Nothing

End Sub

And this works (sorry for dutch names). From here I can go on.

 

[MichaelRed]

Works is good. Using code which you don't fully understand may be good. Neither (in this instance) will be as efficient (read [/]F A S T[/i] as standard SQL (query). As long as the recordset is small, you will probably not note any actiual impact. On even medium sized recordsets, code will noticably impact processing. In particular, looping through recordsets is quite time consuming.





MichaelRed

 

[hstijnen]

OK, I like "nice" solutions.
How to create an index that increments by one? It's not simply a dataset that grows one record per date. There are several different meters each with a value per date. So the index (in the example above) is like "create index INCREM on meterstand (emeter_code, datum)". Or do you mean adding a field Index1 to the table and updating that field every time new records are added?

Next question is how to update the table on basis of a query. The query you suggest is like:
Select m.stand, n.stand, m.verbruik
From m.meterstand, n.meterstand
Where m.Index1 = n.Index1 - 1

But how to formulate the SQL update statement?

Cheers,
Henk

 

[MichaelRed]

re the index you need to use, it depends (obviously) on the intended use. From your brief description, I would hazzard the guess that it needs to be incremented on the METER ID, based on the date. The relationship to show the successive records for the meter can be done in a number of ways. I'm never quite sure which to use in a given situation, but can suggest tw:

First is the "Self Join", this is just two copies of the same recoredset as the source, wit a relationship between the Indexed values of interest. An easy approach to this -for the specifics of from what I have gleaned- would be two queries aginst the TABLE, where one of them has the Index in a calculated fiele where the Index is just incremented by one. Join the two queries on the meter Id and the Index and calculated index (with the uniary increment). To refine this even a bit further, filter hte first two with a parameter (the Meter ID), so you get (in the third query) a recordset of the SINGLE Meter ID and the readings from successive readings.

The se4cond does much hte same, but uses a subquery in the criteria row based on the Index (or the incremented index). The subquery would limit the recordrelationships to the incremented record -for each record in the "primary" query.

Ye olde PHV (who responded earlier) seems to be one of the participants in these fora who does these better than most. In my work environment, I seldom have a real use for them, so have lost much of the meager knowledge I once had (time for me to get re-fresher training?).

In the worst case, I would suggest that you simply use his (OPHV's) handle as a keyword in the search engine and look at the multitudenous examples already present in those archives. A somewhat more ideal soloution (only because you might learn more for your own future use) would be to acquire a text on SQL and study it (as I must AGAIN)!



MichaelRed

 

[PHV]

Storing derived/calculated values is discouraged.
You don't need the verbruik field as you can retrieve (accurate) values with a pure SQL like this:

SELECT A.emeter_code, A.datum, A.stand, A.stand-Nz(B.stand,A.stand) As verbruik
FROM meterstand AS A LEFT JOIN meterstand AS B ON A.emeter_code=B.emeter_code AND A.datum>B.datum
WHERE Nz(B.datum,0)=Nz((SELECT Max(datum) FROM meterstand WHERE emeter_code=A.emeter_code AND datum<A.datum),0);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

And if you're sure the interval between two successive moments is always one month, here a simpler version:
SELECT A.emeter_code, A.datum, A.stand, A.stand-Nz(B.stand,A.stand) AS verbruik
FROM meterstand AS A LEFT JOIN meterstand AS B
ON A.emeter_code=B.emeter_code AND A.datum=DateAdd('m',1,B.datum);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

Actually, the second post only checks that the "Month" is incremented, withoout reference to the actual interval? If so, the rolling avg over some long interval might be correct while individual successive entries could vary considerably.



MichaelRed

 

[PHV]

only checks that the "Month" is incremented
No, I used DateAdd (not DateDiff) so the interval between A.datum and B.datum is EXACTLY one month, i.e. all the dates for a given emeter_code should be on the same day.
The previous suggestion is safer but slower.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886