Calculate the difference for a record over time

[chrismears]

Hi
I am trying to calculate the difference for a record over time.

So far I know the following:
I don't know how much of a resource is used over time, but at the end of
each month, I do know how much has been used from the beginning of the year
to the end of the month. Here's my example:

Picture something like a barrel being filled with water:
When you start, the barrel is empty
After a month there is now 2 litres of water in the barrel
After the second month there is a total of 5 litres of water in the barrel
After the third month there is a total of 9 litres of water in the barrel

What I have:
My table lists each month as a record
For each record I have the amount of water in the barrel

What I need:
Is a query that tells me how much water was put in the barrel per month.
Going back to the example, if the query were in place it would show that:
Month 1 -> 2 litres of water put in the barrel (2 minus 0 = 2)
Month 2 -> 3 litres of water put in the barrel (5 minus 2 = 3)
Month 3 -> 4 litres of water put in the barrel (9 minus 5 = 4)

This is simple for one barrel, in my database we would be recording hundreds
of barrels.

Help!

Thanks in advance, I really appreciate your insight and fresh views.

Chris

 

[Michel Walsh]

So basically, you want to undo a running sum.


SELECT a.barrelID,
a.dateStamp,
LAST(a.RunningSum) AS actualRun,
b.dateStamp,
LAST(b.RunningSum) AS previousRun,
LAST(a.RunningSum) - LAST(b.RunningSum) AS consumed

FROM (myTable AS a INNER JOIN myTable AS b
ON a.barrelID=b.barrelID AND a.dateStamp > b.dateStamp)
INNER JOIN myTable AS c
ON a.barrelID=c.barrelID AND a.dateStamp> c.dateStamp

GROUP BY a.barrelID, a.dateStamp, b.dateStamp

HAVING b.dateStamp = MAX(c.dateStamp)



should do (but can be slow; index barrelID and dateStamp fields).


Hoping it may help,
Vanderghast, Access MVP