How to compute difference between successive records?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hstijnen - 26 Jul 2005 12:52 GMT

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

Reply to this Message

Alex Dybenko - 26 Jul 2005 13:10 GMT

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

Signature

Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

> Hi,
> I've an Access db with a table that records the state of an electricity
[quoted text clipped - 11 lines]
>
> Henk

Reply to this Message

Allen Browne - 26 Jul 2005 13:24 GMT

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210504

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 10 lines]
>
> Henk

Reply to this Message

hstijnen - 27 Jul 2005 11:56 GMT

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
[/code]

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

Reply to this Message

peregenem@jetemail.net - 27 Jul 2005 14:20 GMT

> 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

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;

Reply to this Message