Need an elegant way to calculate difference between numerical records.

[Corwin]

I'm new to building databases and I wouldn't think that this is difficult but it has stumped me and everyone that I have approached to elegantly perform this calculation.

Essentially I have a table of meters (the best real world example is the watermeter that your community uses to measure how much water you use each month.) Each meter in the table has a "child table" of the date/time that the meter was read and the reading on the meter (stored as a number). I need to calculate the difference between the last two readings in the child table for each meter(essentially how much water each meter has used since the last reading.)

I have been able to sum, average, etc. the meter readings, but I can't seem to find a way to get a difference.

Thanks in advance,

Corwin

 

[ajetrumpet]

I have been able to sum, average, etc. the meter readings, but I can't seem to find a way to get a difference.

Sum and Average are field functions that enable you to perform the calculations vertically. If you create an expression as a temporary field in a query, it is performed on records (horizontal data), not fields (vertical).

Does your structure look like....

Meter Amount Date

Meter1 (amount1) 1/1/2007
Meter1 (amount2) 2/1/2007

etc, etc...???

To get a query to easily perform what you want, you might consider transposing your data to reflect yearly numbers...??

Meter Jan Feb

Meter1 (amount1) (amount2)
Meter2 (amount1) (amount2)


Then an expression could be written in a query to reflect the incremental variations, such as (amount2-amount1), etc...

There is a function in the Code Repository here that transposes table data, if you're interested. It is from a Microsoft KB article, and works just fine (if you can read it, that is!!)

 

[Corwin]

The structure that you have shown is accurate...I haven't thought of transposing the data. I will see if I can find the function and give it a try.

I will keep you posted and thanks for your help.

 

[Corwin]

I was able to find the function that ajetrumpet suggested, but I don't uderstand how to make it work. Where do I enter the code to make the function work? Do I need to change the datatypes? (I'm working with numbers, not strings) Again, I'm a noobie at this and probably just need a nudge in the right direction. I appreciate your patience and understanding.

Also, in the posting that contained the function, someone had mentioned that this could also be done by inport/export through Excel. Whould that be a better solution? I was hoping that there would be a simple, easy way to do this (utility companies seem to do it all the time.)

Thanks

 

[ajetrumpet]

Corwin, create a copy of the table you want to transpose, and then write the code as a function (just like it's stated) in a separate module, and run it. I have bolded and put in red the words in the function code that need to be substituted with your specific object names.

Function Transposer(strSource As String, strTarget As String)

   Dim db As DAO.Database
   Dim tdfNewDef As DAO.TableDef
   Dim fldNewField As DAO.Field
   Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
   Dim i As Integer, j As Integer

   On Error GoTo Transposer_Err

   Set db = CurrentDb()
   Set rstSource = db.OpenRecordset([COLOR="Red"][B]strSource[/B][/COLOR])
   rstSource.MoveLast

   ' Create a new table to hold the transposed data.
   ' Create a field for each record in the original table.
   Set tdfNewDef = db.CreateTableDef([COLOR="Red"][B]strTarget[/B][/COLOR])
   For i = 0 To rstSource.RecordCount
      Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
      tdfNewDef.Fields.Append fldNewField
   Next i
   db.TableDefs.Append tdfNewDef

   ' Open the new table and fill the first field with
   ' field names from the original table.
   Set rstTarget = db.OpenRecordset([B][COLOR="Red"]strTarget[/COLOR][/B])
   For i = 0 To rstSource.Fields.Count - 1
      With rstTarget
        .AddNew
        .Fields(0) = rstSource.Fields(i).Name
        .Update
      End With
   Next i

   rstSource.MoveFirst
   rstTarget.MoveFirst
   ' Fill each column of the new table
   ' with a record from the original table.
   For j = 0 To rstSource.Fields.Count - 1
      ' Begin with the second field, because the first field
      ' already contains the field names.
      For i = 1 To rstTarget.Fields.Count - 1
         With rstTarget
            .Edit
            .Fields(i) = rstSource.Fields(j)
            rstSource.MoveNext
            .Update
         End With

      Next i
      rstSource
      rstTarget
   Next j

   db.Close

   Exit Function

Transposer_Err:

   Select Case Err
      Case 3010
         MsgBox "The table " & strTarget & " already exists."
      Case 3078
         MsgBox "The table " & strSource & " doesn't exist."
      Case Else
         MsgBox CStr(Err) & " " & Err.Description
   End Select

   Exit Function

End Function

StrSource = Table NAME of which to transpose data.

StrTarget = NAME of new table that will be created to display the transposed data (just make up one).

 

[Corwin]

Apologies...but I'm obviously tackling something beyond my skill level (willing to learn though.)

I quickly created a small database and populated it with some data for testing. I copied the function, made the changes (I hope correctly), and saved as a module in the test database. Then I created a query to try to call the transposer function and calculate the difference. I cannot get the module to run, the function is undefined. I have tried this in several different ways, and the books which I have simply state that modules are "the basis for advanced access programming" if they are mentioned at all.

I have attached a zipped copy of the test database so you will be able to see where I'm going wrong...

Thanks

 

[Brianwarnock]

You can not give a function the same name as the module call it say fTransposer.

Plus the Fumction has 2 input arguments, you do not show any in the query.

Just a quick look

Brian

 

[pbaldy]

I would do it with SQL:

SELECT MeterReadings.MeterID, MeterReadings.Date, MeterReadings.Reading, (SELECT Reading FROM MeterReadings AS Alias WHERE Alias.MeterID = MeterReadings.MeterID AND Alias.Date = (SELECT Max(Date) FROM MeterReadings AS Alias2 WHERE Alias2.MeterID = MeterReadings.MeterID AND Alias2.Date < MeterReadings.Date)) AS PrevReading, [Reading]-[PrevReading] AS Diff
FROM MeterReadings
ORDER BY MeterReadings.MeterID, MeterReadings.Date

 

[Corwin]

I'm still struggling with the function, but the SQL code works nicely (thanks pbaldy) except for one thing...I'm looking for the difference for the last reading, not for all of them. I'm currently trying to sort/filter the SQL query to end up with just the last reading. (This is the first time I've touched SQL so this will most likely be a learning experience as well.)

Thanks

 

[The_Doc_Man]

Corwin, your problem is that SUM and MAX and MIN and AVG and those other guys are all aggregate functions for which the property of commutativity applies. For DIFFERENCES, this is not true. The underlying problem, and it is deep-seated in theory, is that technically there IS no order in a data set until you impose it with a query. According to SET theory, everything you do in SQL should be considered MONOLITHIC - i.e. no fine structure. So the concept of ORDER - as in "difference between adjacent readings" has no meaning because ADJACENCY, which implies ordering, also has no meaning.

There ARE ways to do this purely with SQL, but they are ugly. The "right" way to do this is via VBA reading the recordset and computing the differences on the fly for you.

Search this forum for a discussion of NEXT RECORD and PREVIOUS RECORD as topics to see the theoretical basis and some workarounds for this problem.

 

[pbaldy]

I'm still struggling with the function, but the SQL code works nicely (thanks pbaldy) except for one thing...I'm looking for the difference for the last reading, not for all of them.

Okay, a 2 query solution. First one named qryMaxReadings finds the last reading for each meter:

SELECT Meters.MeterID, Max(MeterReadings.Date) AS MaxOfDate
FROM Meters INNER JOIN MeterReadings ON Meters.MeterID = MeterReadings.MeterID
GROUP BY Meters.MeterID;

Then this one uses that:

SELECT [MeterReadings].[MeterID], [MeterReadings].[Date], [MeterReadings].[Reading], (SELECT Reading FROM MeterReadings AS Alias WHERE Alias.MeterID = MeterReadings.MeterID AND Alias.Date = (SELECT Max(Date) FROM MeterReadings AS Alias2 WHERE Alias2.MeterID = MeterReadings.MeterID AND Alias2.Date < MeterReadings.Date)) AS PrevReading, [Reading]-[PrevReading] AS Diff
FROM qryMaxReadings INNER JOIN MeterReadings ON ([qryMaxReadings].[MeterID]=[MeterReadings].[MeterID]) AND ([qryMaxReadings].[MaxOfDate]=[MeterReadings].[Date])
ORDER BY [MeterReadings].[MeterID], [MeterReadings].[Date];

 

[Corwin]

Thanks pbaldy....that's exactly what I was looking for. You get the gold star. I appreciate the help that I have received from all of you.

Thanks Again,

Corwin

 

[pbaldy]

Excellent! Mommy will be proud of me.