Hi all,
I have a dataset which lists consecutive snapshot readings grouped by different sources.
Example dataset
PK Location Source Timestamp ReadingValue_1 Reading_Value_2
1 HouseA GasMeter 12/1/18 08:00 123.12 146.12
2 HouseA GasMeter 12/1/18 08:15 124.15 154.23
3 HouseB GasMeter 11/15/18 09:00 46.35 100.98
4 HouseB GasMeter 11/15/18 09:015 48.34 102.45
5 HouseB WaterMeter 1/15/18 19:00 46.35 100.98
6 HouseB WaterMeter 1/15/18 19:15 58.34 132.45
The PK is an autonumber.
Goal: I would like to subtract consecutive readings from each other.
For example for HouseA:
ReadingsValue_1_Diff = 124.15 – 123.12
ReadingsValue_2_Diff = 154.23 – 146.12
This is a large dataset. There are roughly 35,000 readings for each Location/Source combination.
I’ve tried to do this before with subqueries and found it to be very slow for large datasets, so I’m attempting to do it using a VBA function which I call from a query, but having some trouble.
My approach is to:
When I call this code from the query it freezes up MS Access and I must force an exit.
Can someone help me with what I’m doing wrong.
Many Thanks
I have a dataset which lists consecutive snapshot readings grouped by different sources.
Example dataset
PK Location Source Timestamp ReadingValue_1 Reading_Value_2
1 HouseA GasMeter 12/1/18 08:00 123.12 146.12
2 HouseA GasMeter 12/1/18 08:15 124.15 154.23
3 HouseB GasMeter 11/15/18 09:00 46.35 100.98
4 HouseB GasMeter 11/15/18 09:015 48.34 102.45
5 HouseB WaterMeter 1/15/18 19:00 46.35 100.98
6 HouseB WaterMeter 1/15/18 19:15 58.34 132.45
The PK is an autonumber.
Goal: I would like to subtract consecutive readings from each other.
For example for HouseA:
ReadingsValue_1_Diff = 124.15 – 123.12
ReadingsValue_2_Diff = 154.23 – 146.12
This is a large dataset. There are roughly 35,000 readings for each Location/Source combination.
I’ve tried to do this before with subqueries and found it to be very slow for large datasets, so I’m attempting to do it using a VBA function which I call from a query, but having some trouble.
My approach is to:
- Create a new query to organize the data and call the VBA function
- Create a function with a single argument to capture the PK
- Create a recordset based on the datasource table
- The SQL statement orders the data by location/source and timestamp
- Sync-up the recordset to the query using rs.Move
- Check to see if the Location/Source combination
- Find the next record values using rs.MoveNext
- Perform the subtraction calculation
Code:
Public Function MeterReadings(PKID As Integer) As Variant
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sSql As String
Dim CurrentLocation As String
Dim CurrentSource As String
Dim NextLocation As String
Dim NextSource As String
Dim CurrentReading As String
Dim NextReading As String
sSql = "SELECT * FROM tbl_Readings PI ORDER BY Location, Source, TimeStamp"
Set cnn = CurrentProject.Connection
rs.Open sSql, cnn
rs.Move PKID - 1
If rs.EOF <> True Then
CurrentSource = rs!Location
CurrentLocation = rs!Source
rs.MoveNext
NextLocation = rs!Location
NextSource = rs!Source
If (CurrentLocation = NextLocation) Then
If (CurrentSource = NextSource) Then
MeterReadings = (NextReading - CurrentReading)
End If
End If
End If
rs.Close
cnn.Close
End Function
When I call this code from the query it freezes up MS Access and I must force an exit.
Can someone help me with what I’m doing wrong.
Many Thanks