Calculate difference from one record to next

[scchas]

I am setting up application for determining electricity usage and I need to know how to calculate the difference of the meter reading this week against the meter reading of the previous week. I am not sure I am setting up the table properly to do this.

Thanks in advance for any helpful advice.

 

[THWatson]

scchas
You wouldn't need to store the "difference" value. Just keep a date the reading was taken along with the meter reading. Then in a query you can always calculate the difference between two readings.

Tom

 

[scchas]

Thanks, but I do understand that. How would I do the calculation in the query? That's what I really need to know. I didn't make that clear in the original message.

 

[THWatson]

scchas
Sorry, I have been out for several hours, so just picked up your reply.

Here's one way of doing what you want in a query.

Use a Totals query with 2 columns. Both columns will have Expression in the Total row. (I am using MeterRead and ReadDate as the two fields; you will have to rename those to match your table's fields)

One column will have the following expression as its field...
Difference: Max([MeterRead])-Min([MeterRead])

The second column will have the ReadDate as its field, and the following expression in its criteria row...
Between [Start Date] And [End Date]

That will give you the difference in readings between any two dates selected.

Hope that helps.

Tom

 

[PHV]

You may try something like this:
SELECT A.ReadDate, A.MeterRead, A.MeterRead - (SELECT B.MeterRead FROM yourTable B WHERE B.ReadDate = (SELECT Max(C.ReadDate) FROM yourTable C WHERE C.ReadDate < A.ReadDate)) As Difference

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[THWatson]

PHV
For my own edification, could you explain the method used in your reply? I have tried to make your SQL work, but to no avail. I keep getting error 3025.

Thanks.

Tom

 

[PHV]

Oops, missed the FROM clause:
SELECT A.ReadDate, A.MeterRead, A.MeterRead - (SELECT B.MeterRead FROM yourTable B WHERE B.ReadDate = (SELECT Max(C.ReadDate) FROM yourTable C WHERE C.ReadDate < A.ReadDate)) As Difference
FROM yourTable A;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

If the above doesn't work, create a saved query named,say qryPrevDate:
SELECT A.ReadDate, Max(B.ReadDate) As PrevDate
FROM yourTable A INNER JOIN yourTable B ON B.ReadDate<A.ReadDate;
Then you may try this:
SELECT A.ReadDate, A.MeterRead, A.MeterRead - Nz(B.MeterRead,A.MeterRead) As Difference
FROM yourTable A
LEFT JOIN (qryPrevDate P INNER JOIN yourTable B ON P.PrevDate=B.ReadDate)
ON A.ReadDate=P.ReadDate)
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

OOps hit submit too fast ...
If the above doesn't work, create a saved query named,say qryPrevDate:
SELECT A.ReadDate, Max(B.ReadDate) As PrevDate
FROM yourTable A INNER JOIN yourTable B ON B.ReadDate<A.ReadDate;
Then you may try this:
SELECT A.ReadDate, A.MeterRead, A.MeterRead - Nz(B.MeterRead,A.MeterRead) As Difference
FROM yourTable A
LEFT JOIN (qryPrevDate P INNER JOIN yourTable B ON P.PrevDate=B.ReadDate)
ON A.ReadDate=P.ReadDate
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

With some minor fiddling, PHVs' soloution (from about the third previous post) can be made to return the values as requested. While it is a good starting point, reality suggests that the readings will NOT be taken exactly one-week apart for very long. To reflect the usage in a uniform manner, you would need to add a field to note the number of dayd between readings and then do the math to get the 'daily' usage. It is easy enough to clone / modify PHVs' "Difference" field to create a "DaysDifference" field, and the math is just the expression of the "Difference" field / "DaysDifference" Field.




MichaelRed

 

[THWatson]

PHV
Your SQL...

SELECT A.ReadDate, A.MeterRead, A.MeterRead - (SELECT B.MeterRead FROM yourTable B WHERE B.ReadDate = (SELECT Max(C.ReadDate) FROM yourTable C WHERE C.ReadDate < A.ReadDate)) As Difference 
FROM yourTable A;

works perfectly. Just substitute the correct table name for "yourTable" and it runs. Thanks for the clarification.

Tom

 

[scchas]

Thanks for your help. This appears to work.