How to compute difference between successive records?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi,
i think you can try to open a recordset, based on your table, and sorted by
date, then loop through it, keep value of previous record in variable and
use it for calculation. Beleive that Access help provides several examples
using recordset
 
Thanks for your help!
I've found some code at msdn and now my code is something like
Code: 
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.
 
hstijnen said:
Thanks for your help!
I've found some code at msdn and now my code is something like
Code: 
strSQL = "SELECT emeter_code, datum, stand, verbruik FROM meterstand order
by emeter_code, datum"
Do While Not .EOF[/QUOTE]

There's no need to resorts to procedural code (Do While Not EOF) and
cursors (ORDER BY or Sort). A set-based SQL solution is possible.
Hopefully this simple (English language) example illustrates the point

CREATE TABLE Test (
key_col INTEGER NOT NULL,
date_col DATETIME NOT NULL,
data_col INTEGER NOT NULL);

INSERT INTO Test VALUES (1, #2001-01-01#, 1);
INSERT INTO Test VALUES (1, #2002-01-01#, 2);
INSERT INTO Test VALUES (1, #2003-01-01#, 3);
INSERT INTO Test VALUES (2, #2001-02-01#, 1);
INSERT INTO Test VALUES (3, #2001-03-01#, 1);
INSERT INTO Test VALUES (3, #2002-03-01#, 2);

SELECT T1.key_col, T1.date_col, T1.data_col,
(SELECT MAX(date_col) FROM Test
WHERE T1.key_col = key_col
AND date_col < T1.date_col) AS prev_date,
(SELECT T2.data_col FROM Test AS T2
WHERE T2.key_col = T1.key_col
AND T2.date_col =
(SELECT MAX(date_col) FROM Test
WHERE T1.key_col = key_col AND date_col < T1.date_col)
) AS prev_value FROM Test AS T1;
Click to expand...
 
Back
Top