If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
in access how to get the running difference between records
I have data stored in access table in thee fields [eqptId] , [date],
[monthly reading] How to get the [monthly reading] difference between each record? |
#2
|
|||
|
|||
in access how to get the running difference between records
You will need to give more information about your datatypes and application
-- Wayne Trentino, Italia. "kudur" wrote: I have data stored in access table in thee fields [eqptId] , [date], [monthly reading] How to get the [monthly reading] difference between each record? |
#3
|
|||
|
|||
in access how to get the running difference between records
I'm not sure if this is the sort of thing you are looking for but the
following is the SQL for a query I wrote many years ago based on table Readings with columns ReadingDate, ReadingType (gas, electricity etc) and Reading: SELECT R1.ReadingType, R1.ReadingDate, R1.ReadingDate - (SELECT MAX(R2.ReadingDate) FROM Readings As R2 WHERE R2.ReadingDate R1.ReadingDate AND ReadingType = R1.ReadingType) AS DaysSinceLastReading, R1.Reading - (SELECT MAX(R3.Reading) FROM Readings As R3 WHERE R3.ReadingDate R1.ReadingDate AND ReadingType = R1.ReadingType) AS [Usage] FROM Readings AS R1 WHERE R1.ReadingDate (SELECT MIN(R4.ReadingDate) FROM Readings As R4 WHERE ReadingType = R1.ReadingType) ORDER BY R1.ReadingType, R1.ReadingDate; If you want the difference between each reading for each eqptId then if you change the SQL statement so that the references to Readings are to your table name, those to ReadingType are to eqptId and those to Reading are to [monthly reading] it should work with your data. It also gives you the time in days between each reading as well as the difference in units between the readings. If you want to show the readings themselves, including the initial reading for each ReadingType change it to: SELECT R1.ReadingType, R1.ReadingDate, R1.Reading, R1.ReadingDate - (SELECT MAX(R2.ReadingDate) FROM Readings As R2 WHERE R2.ReadingDate R1.ReadingDate AND ReadingType = R1.ReadingType) AS DaysSinceLastReading, R1.Reading - (SELECT MAX(R3.Reading) FROM Readings As R3 WHERE R3.ReadingDate R1.ReadingDate AND ReadingType = R1.ReadingType) AS [Usage] FROM Readings AS R1 ORDER BY R1.ReadingType, R1.ReadingDate; Ken Sheridan Stafford, England "kudur" wrote: I have data stored in access table in thee fields [eqptId] , [date], [monthly reading] How to get the [monthly reading] difference between each record? |
Thread Tools | |
Display Modes | |
|
|